Bug #62350 mysql_stmt_result_metadata() returns invalid metadata on EXPLAIN, can crash PHP
Submitted: 5 Sep 2011 12:13 Modified: 7 Sep 2011 4:49
Reporter: Ulf Wendel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5/5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D1 (Critical)

[5 Sep 2011 12:13] Ulf Wendel
Description:
mysql_stmt_result_metadata() can return invalid metadata for EXPLAIN statements. This will crash (all Connector/)PHP in the following. The issue can be reproduced with the below given C program.

Note that only certain libmysql versions are affected. A quick check has shown:

  - Tonci @ Windows        @ 5.1.43: affected
  - Ulf @ OpenSuSE         @ 5.1.49 : not affected
  - Andrey @ Ubuntu 11.04  @ 5.1.52: affected
  - Ulf @ OpenSuSE         @ 5.6.2-m5: hits assertion/affected

To verify, you are kindly requested to test against multiple version.

Note also that test code run by the assorted people involved had been slightly different, thus you find slightly different output. However, the issue is clearly there.

Very likely, this is a libmysql issue, see also https://bugs.php.net/bug.php?id=55001 . The php.net bug does not speak about crashes but we found crashes, caused by wrong meta data, as well. 

How to repeat:
#include "stdio.h"
#include "stdlib.h"
#include "string.h"
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>

/*
nixnutz@linux-fuxh:~/mysql_api> echo $CFG; echo ""; sh -c "gcc -o progname `$CFG --cflags` statement_explain_meta.c `$CFG --libs` && ./progname"
/home/nixnutz/ftp/mysql-5.6.2-m5/install/bin/mysql_config

Server:         5.6.2-m5-debug
Protocol:       10
Client:         5.1.49

         column = 'ALL'
         length = 3
         stmt->fields[3].type = 253
         stmt->fields[3].max_length = 15
         stmt->fields[3].length = 30
         NOTE: data has been truncated!
         sqlstate = '00000'

         column = 'unique_subquery'
         length = 15
         stmt->fields[3].type = 253
         stmt->fields[3].max_length = 15
         stmt->fields[3].length = 30
         NOTE: data has been truncated!
         sqlstate = '00000'

*/

int exit_failure(const char *msg, MYSQL *mysql, MYSQL_RES *res, MYSQL_STMT *stmt);

/* Connection parameter - change */
const char *con_host = "localhost";
const char *con_user = "root";
const char *con_pass = "";
const char *con_db = "test";
unsigned int con_port = 3306;
const char *con_socket = "/tmp/mysql.sock";
unsigned long con_flags = 0;
const char* show_authors = "SHOW AUTHORS";
const char* show_chars = "SHOW DATABASES";

const char* drop = "DROP TABLE IF EXISTS mytest_table";
const char* create = "CREATE TABLE mytest_table(id INT PRIMARY KEY NOT NULL, idParent INT)";
const char* insert = "INSERT INTO mytest_table(id, idParent) VALUES (1, -1), (2, 1)";
const char* explain = "EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table);";

int main(void) {
	MYSQL *conn;
	MYSQL_STMT *stmt;
	MYSQL_RES  *res;
	MYSQL_FIELD *column;

	MYSQL_BIND bind[10];
	// int column_value;
	char column_value[255];
	unsigned long length;
	int i;
	my_bool is_null;
	my_bool error;
	my_bool attr_get;

	unsigned int ret;

	printf("\n");

	conn = mysql_init(NULL);
	if (conn == NULL)
		exit_failure("mysql_init() failed", NULL, NULL, NULL);

	if (mysql_real_connect(conn, con_host, con_user, con_pass, con_db, con_port, con_socket, con_flags) == NULL)
		exit_failure("mysql_real_connect() failed", conn, NULL, NULL);

	printf("Server: 	%s\n", mysql_get_server_info(conn));
	printf("Protocol: 	%u\n", mysql_get_proto_info(conn));
	printf("Client: 	%s\n\n", mysql_get_client_info());

	if (mysql_query(conn, drop) ||
		mysql_query(conn, create) ||
		mysql_query(conn, insert))
		exit_failure("Setup failed", conn, NULL, NULL);

	stmt = mysql_stmt_init(conn);
	if (mysql_stmt_prepare(stmt, explain, strlen(explain)))
		exit_failure("mysql_stmt_prepare() failed", conn, NULL, stmt);

	if (mysql_stmt_execute(stmt))
		exit_failure("mysql_stmt_execute() failed", conn, NULL, stmt);

	attr_get = 1;
	if (mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, &attr_get))
		exit_failure("mysql_stmt_attr_set() failed", conn, NULL, stmt);

	if (mysql_stmt_store_result(stmt))
		exit_failure("mysql_stmt_store_result() failed", conn, NULL, stmt);

	if (res = mysql_stmt_result_metadata(stmt)) {
		printf("Executed %s returned meta...\n", explain);
		while (column = mysql_fetch_field(res)) {
			printf("\tname = %s\n", column->name);
			printf("\t\ttype = %lu\n", column->type);
			printf("\t\tdb_length = %u\n", column->db_length);
			printf("\t\tlength = %lu\n", column->length);
			printf("\t\tmax_length = %lu\n", column->max_length);
			printf("\n");
		}
		mysql_free_result(res);
	} else {
		printf("Executed %s returned no meta - error: '%s'\n", show_authors, mysql_stmt_error(stmt));
	}

	memset(bind, 0, sizeof(bind));
	bind[3].buffer_type =  MYSQL_TYPE_VAR_STRING;
	bind[3].buffer = (char*)column_value;
	bind[3].buffer_length = 255;
	bind[3].is_null = &is_null;
	bind[3].length = &length;
	bind[3].error = &error;

	if (mysql_stmt_bind_result(stmt, bind))
		exit_failure("mysql_stmt_bind_result() failed", conn, NULL, stmt);

	while (MYSQL_NO_DATA != (ret = mysql_stmt_fetch(stmt))) {
		printf("\t column = '%s'\n", column_value);
		printf("\t length = %lu\n", (long unsigned int)length);
		printf("\t stmt->fields[3].type = %lu\n", (long unsigned int)stmt->fields[3].type);
		printf("\t stmt->fields[3].max_length = %lu\n", (long unsigned int)stmt->fields[3].max_length);
		printf("\t stmt->fields[3].length = %lu\n", (long unsigned int)stmt->fields[3].length);
		if (ret == MYSQL_DATA_TRUNCATED)
			printf("\t NOTE: data has been truncated!\n");
		printf("\t sqlstate = '%s'\n", mysql_stmt_sqlstate(stmt));
		printf("\n");
	}

	mysql_stmt_close(stmt);
	mysql_close(conn);
	printf("\n");

	return EXIT_SUCCESS;
}

int exit_failure(const char *msg, MYSQL *mysql, MYSQL_RES *res, MYSQL_STMT *stmt) {
	printf("ERROR: %s\n", msg);
	if (stmt) {
		printf("[%u] %s\n", mysql_stmt_errno(stmt), mysql_stmt_error(stmt));
		mysql_stmt_close(stmt);
	}
	if (mysql) {
		if (mysql_errno(mysql))
			printf("[%u] %s\n", mysql_errno(mysql), mysql_error(mysql));
		if (res)
			mysql_free_result(res);
		mysql_close(mysql);
	}
	printf("\n");
	exit(EXIT_FAILURE);
}

Suggested fix:
------------------ example output from Tonci ------------------------------

1st run:
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.43-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
110902 18:54:06        1 Connect    root@localhost on test
            1 Query    DROP TABLE IF EXISTS mytest_table
            1 Query    CREATE TABLE mytest_table(id INT PRIMARY KEY NOT NULL, idParent INT)
            1 Query    INSERT INTO mytest_table(id, idParent) VALUES (1, -1), (2, 1)
            1 Prepare    EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table)
            1 Execute    EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table)
            1 Close stmt   
            1 Quit   

What I see in Data directory is some crashed temporary stuff:
 Directory of F:\MySQL\Data\MySQL Server 5.1\data

02.09.11  18:54    <DIR>          .
02.09.11  18:54    <DIR>          ..
04.09.09  09:00    <DIR>          bug36370
02.09.11  18:54        52.428.800 ibdata1
02.09.11  18:54        56.623.104 ib_logfile0
02.09.11  18:54        56.623.104 ib_logfile1
04.09.09  09:56    <DIR>          jshop
02.09.10  14:59    <DIR>          mydatabase@00201@002e0@002e1@002e0        <<<<<<<<<<
29.01.10  12:09    <DIR>          mysql
02.09.11  18:54             5.598 QCW2K8.err
02.09.11  18:54               956 QCW2K8.log
02.09.11  18:54                 5 QCW2K8.pid
07.05.09  14:04    <DIR>          rimc2
18.08.09  11:55    <DIR>          solusd
11.06.10  09:40    <DIR>          solusd0
18.08.09  16:55    <DIR>          temp
02.09.11  18:54    <DIR>          test
17.02.10  14:11    <DIR>          test2
26.11.10  09:01    <DIR>          testdb
22.07.11  08:53    <DIR>          tst1
               6 File(s)    165.681.567 bytes
              14 Dir(s)   1.280.204.800 bytes free

Removed crashed stuff, re-run, all seems fine:

Server:         5.1.43-community-log
Protocol:       10
Client:         5.1.43

name = [id]
db = []
db_length = 2779096485
name = [select_type]
db = []
db_length = 2779096485
name = [table]
db = []
db_length = 2779096485
name = [type]
db = []
db_length = 2779096485
name = [possible_keys]
db = []
db_length = 2779096485
name = [key]
db = []
db_length = 2779096485
name = [key_len]
db = []
db_length = 2779096485
name = [ref]
db = []
db_length = 2779096485
name = [rows]
db = []
db_length = 2779096485
name = [Extra]
db = []
db_length = 2779096485

------- Ulf ------------------
^ ATTENTION: db_length bogus
------------------------------

C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.43-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld, Version: 5.1.43-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
110902 18:59:23        1 Connect    root@localhost on test
110902 19:00:27        1 Query    DROP TABLE IF EXISTS mytest_table
110902 19:00:28        1 Query    CREATE TABLE mytest_table(id INT PRIMARY KEY NOT NULL, idParent INT)
            1 Query    INSERT INTO mytest_table(id, idParent) VALUES (1, -1), (2, 1)
110902 19:00:49        1 Prepare    EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table)
            1 Execute    EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table)
110902 19:01:34        1 Close stmt   
            1 Quit   

I used DEBUG libraries from 5.1.43.

Hope this helps, Tonci

-------- Andrey @ Unbuntu 11.04 @ MySQL 5.1.54 self compiled------------------

Server: 	5.1.60
Protocol: 	10
Client: 	5.1.54

name = [id]
db = []
db_length = 540370793
name = [select_type]
db = []
db_length = 1818308412
name = [table]
db = []
db_length = 1886220131
name = [type]
db = []
db_length = 1734437990
name = [possible_keys]
db = []
db_length = 1886351989
name = [key]
db = []
db_length = 1634298977
name = [key_len]
db = []
db_length = 1684368489
name = [ref]
db = []
db_length = 1685221180
name = [rows]
db = []
db_length = 1919247474
name = [Extra]
db = []
db_length = 1684605193

------------- Ulf @ OpenSuSE @ MySQL 5.6.2-m5 self compiled --------------------

nixnutz@linux-fuxh:~/mysql_api> rm ./progname; echo $CFG; echo ""; sh -c "gcc -o progname `$CFG --cflags` statement_explain_meta.c `$CFG --libs` && LD_LIBRARY_PATH=/home/nixnutz//ftp/mysql-5.6.2-m5/install/lib:$LD_LIBRARY_PATH ./progname"                                                                                                                          
rm: Entfernen von „./progname“ nicht möglich: Datei oder Verzeichnis nicht gefunden                                                                                                 
/home/nixnutz/ftp/mysql-5.6.2-m5/install/bin/mysql_config                                                                                                                           

Server:         5.6.2-m5-debug
Protocol:       10
Client:         5.6.2-m5

Executed EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table); returned meta...
        name = id
                type = 8
                db_length = 0
                length = 3
                max_length = 21

        name = select_type
                type = 253
                db_length = 0
                length = 19
                max_length = 18

        name = table
                type = 253
                db_length = 0
                length = 64
                max_length = 12

        name = type
                type = 253
                db_length = 0
                length = 10
                max_length = 15

        name = possible_keys
                type = 253
                db_length = 0
                length = 4096
                max_length = 7

        name = key
                type = 253
                db_length = 0
                length = 64
                max_length = 7

        name = key_len
                type = 253
                db_length = 0
                length = 4096
                max_length = 1

        name = ref
                type = 253
                db_length = 0
                length = 1024
                max_length = 4

        name = rows
                type = 8
                db_length = 0
                length = 10
                max_length = 21

        name = Extra
                type = 253
                db_length = 0
                length = 255
                max_length = 47

progname: /home/nixnutz/ftp/mysql-5.6.2-m5/libmysql/libmysql.c:3906: setup_one_fetch_function: Assertion `param->buffer_length != 0' failed.
sh: Zeile 1: 14754 Abgebrochen             LD_LIBRARY_PATH=/home/nixnutz//ftp/mysql-5.6.2-m5/install/lib:/usr/lib64/mpi/gcc/openmpi/lib64 ./progname
[6 Sep 2011 18:04] Miguel Solorzano
Windows Vista 64-bit today source 5.1 code:

C:\DBS\5.1\bin>62350bug

Server:         5.1.60-Win X64-log
Protocol:       10
Client:         5.1.60

Executed EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table); returned meta...
        name = id
                type = 8
                db_length = 0
                length = 3
                max_length = 21

        name = select_type
                type = 253
                db_length = 0
                length = 19
                max_length = 18

        name = table
                type = 253
                db_length = 0
                length = 64
                max_length = 12

        name = type
                type = 253
                db_length = 0
                length = 10
                max_length = 15

        name = possible_keys
                type = 253
                db_length = 0
                length = 4096
                max_length = 7

        name = key
                type = 253
                db_length = 0
                length = 64
                max_length = 7

        name = key_len
                type = 253
                db_length = 10485856
                length = 4096
                max_length = 1

        name = ref
                type = 253
                db_length = 0
                length = 1024
                max_length = 4

        name = rows
                type = 8
                db_length = 0
                length = 10
                max_length = 21

        name = Extra
                type = 253
                db_length = 0
                length = 255
                max_length = 47

         column = 'ALL'
         length = 3
         stmt->fields[3].type = 253
         stmt->fields[3].max_length = 15
         stmt->fields[3].length = 10
         NOTE: data has been truncated!
         sqlstate = '00000'

         column = 'unique_subquery'
         length = 15
         stmt->fields[3].type = 253
         stmt->fields[3].max_length = 15
         stmt->fields[3].length = 10
         NOTE: data has been truncated!
         sqlstate = '00000'

C:\DBS\5.1\bin>
[6 Sep 2011 18:22] Ulf Wendel
I think you verified it:

    name = key_len
                type = 253
                db_length = 10485856
                length = 4096
                max_length = 1

Note the db_length.
[7 Sep 2011 4:49] Miguel Solorzano
Windows Vista 64-bit 5.5 source tree:

C:\DBS\5.5\bin>62350bug.exe

Server:         5.5.17-log
Protocol:       10
Client:         5.5.17

Executed EXPLAIN SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table); returned meta...
        name = id
                type = 8
                db_length = 0
                length = 3
                max_length = 21

        name = select_type
                type = 253
                db_length = 0
                length = 19
                max_length = 18

        name = table
                type = 253
                db_length = 0
                length = 64
                max_length = 12

        name = type
                type = 253
                db_length = 0
                length = 10
                max_length = 15

        name = possible_keys
                type = 253
                db_length = 0
                length = 4096
                max_length = 7

        name = key
                type = 253
                db_length = 0
                length = 64
                max_length = 7

        name = key_len
                type = 253
                db_length = 0
                length = 4096
                max_length = 1

        name = ref
                type = 253
                db_length = 0
                length = 1024
                max_length = 4

        name = rows
                type = 8
                db_length = 0
                length = 10
                max_length = 21

        name = Extra
                type = 253
                db_length = 0
                length = 255
                max_length = 47

         column = 'ALL'
         length = 3
         stmt->fields[3].type = 253
         stmt->fields[3].max_length = 15
         stmt->fields[3].length = 10
         NOTE: data has been truncated!
         sqlstate = '00000'

         column = 'unique_subquery'
         length = 15
         stmt->fields[3].type = 253
         stmt->fields[3].max_length = 15
         stmt->fields[3].length = 10
         NOTE: data has been truncated!
         sqlstate = '00000'

C:\DBS\5.5\bin>