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