Description:
This happens when the C API for prepared statements is used.
We prepare 97 or more statements for later usage in our software.
After all statements are prepared we execute the statements.
The database server does not execute the given statement correct all the time.
Instead another prepared statement will be executed by the database server.
e.g.
stmt nr[64] should be executed, but the server is executing stmt nr[96]
It was recognized that this problem is related to the character set of the database.
We are using :
CHARACTER SET latin1 COLLATE latin1_bin
Debugging the database shows that a statement_id is used to find the related server statement in a hash table. The calculation of the hash index returns the
same index for some statement_id. The consequence is that a wrong statement
will be executed.
I assume the problem is because the used character set maps some characters.
The statement_id is a numeric value and not a string, so it looks very strange to me why this was done.
However, i did what i described in "Suggested fix" to solve my problem temporary.
A complete reproduction code is given in the "How to repeat" part.
Regards ...
Joerg Rueschenschmidt
How to repeat:
// ATTENTION:
//
// Your database needs to be CHARACTER SET latin1 COLLATE latin1_bin
//
#include<windows.h>
#include <stdlib.h>
#include <mysql.h>
#define NUM_OF_USED_STMT 97
#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS t1"
#define CREATE_SAMPLE_TABLE "CREATE TABLE t1( a INTEGER ) TYPE = INNODB"
#define INSERT_VALUE "INSERT INTO t1 VALUES (%d)"
#define SELECT_VALUE "SELECT a FROM t1 WHERE a = %d"
void do_test( MYSQL * mysql )
{
MYSQL_STMT * stmt[NUM_OF_USED_STMT];
/* used to bind the result */
MYSQL_BIND bind[NUM_OF_USED_STMT];
int int_data[NUM_OF_USED_STMT];
unsigned long length[NUM_OF_USED_STMT];
my_bool is_null[NUM_OF_USED_STMT];
/* some help variables */
int i;
char sql_cmd[ 256 ];
/*
create test environment
*/
if( mysql_query(mysql, DROP_SAMPLE_TABLE ) ) {
printf("ERR : Drop table failed (%s)\n", mysql_error(mysql));
return;
}
if( mysql_query(mysql, CREATE_SAMPLE_TABLE ) ) {
printf("ERR : Create table error (%s)\n", mysql_error(mysql));
return ;
}
for ( i = 0; i < NUM_OF_USED_STMT; i++ ) {
sprintf( sql_cmd, INSERT_VALUE, i);
if( mysql_query(mysql, sql_cmd ) ) {
printf("ERR : Insert failed (i = %d) !\n", i );
return ;
}
}
/*
init and prepare all statements used in this test
*/
for ( i = 0; i < NUM_OF_USED_STMT; i++ ) {
if ( !(stmt[i] = mysql_stmt_init(mysql) ) ) {
printf("ERR : mysql_stmt_init() failed (i=%d, err= %s) !\n",
i, mysql_error(mysql));
return ;
}
sprintf( sql_cmd, SELECT_VALUE, i );
if ( mysql_stmt_prepare( stmt[ i ], sql_cmd, strlen( sql_cmd ) ) ) {
printf( "ERR : mysql_stmt_prepare() failed (i=%d, err = %s)\n",
i, mysql_error(mysql));
return ;
}
}
/*
init the bind array for the results
*/
for ( i = 0; i < NUM_OF_USED_STMT; i++ ) {
bind[i].buffer_type= MYSQL_TYPE_LONG;
bind[i].buffer= (char *)&int_data[i];
bind[i].is_null= &is_null[i];
bind[i].length= &length[i];
}
/*
execute statement and test result
*/
for ( i = 0; i < NUM_OF_USED_STMT; i++ ) {
if ( mysql_stmt_execute( stmt[ i ] ) ) {
printf("ERR : mysql_stmt_execute() failed (i=%d, err= %s)\n",
i , mysql_error(mysql));
return ;
}
if ( mysql_stmt_bind_result( stmt[ i ], &bind[i] ) ) {
printf("ERR : mysql_stmt_bind_result() failed (i=%d, err= %s)\n",
i ,mysql_error(mysql));
return;
}
while (! mysql_stmt_fetch( stmt[ i ] ) ) {
if( i != int_data[ i ] ) {
printf("Wrong result in stmt[%d]: (a= %d) should (a= %d)\n",
i, int_data[i], i);
}
}
}
}
main()
{
MYSQL * mysql = mysql_init( NULL );
if ( ! mysql_real_connect( mysql,
/* host */ "localhost",
/* user */ "root",
/* pwd */ "",
/* dbase */ "test",
/* uint port */ 0,
/* char * socket */ NULL,
/* client_flag */ 0 ) )
{
printf(" mysql_real_connec() failed\n");
exit(0);
}
do_test( mysql );
}
Suggested fix:
Modification in sql_class.cpp :
Statement_map::Statement_map() :
last_found_statement(0)
{
enum
{
START_STMT_HASH_SIZE = 16,
START_NAME_HASH_SIZE = 16
};
/* replace default_charset_info with &my_charset_bin now
hash_init(&st_hash, default_charset_info, START_STMT_HASH_SIZE, 0, 0,
get_statement_id_as_hash_key,
delete_statement_as_hash_key, MYF(0));
*/
hash_init(&st_hash, &my_charset_bin, START_STMT_HASH_SIZE, 0, 0,
get_statement_id_as_hash_key,
delete_statement_as_hash_key, MYF(0));
hash_init(&names_hash, &my_charset_bin, START_NAME_HASH_SIZE, 0, 0,
(hash_get_key) get_stmt_name_hash_key,
NULL,MYF(0));
}
Description: This happens when the C API for prepared statements is used. We prepare 97 or more statements for later usage in our software. After all statements are prepared we execute the statements. The database server does not execute the given statement correct all the time. Instead another prepared statement will be executed by the database server. e.g. stmt nr[64] should be executed, but the server is executing stmt nr[96] It was recognized that this problem is related to the character set of the database. We are using : CHARACTER SET latin1 COLLATE latin1_bin Debugging the database shows that a statement_id is used to find the related server statement in a hash table. The calculation of the hash index returns the same index for some statement_id. The consequence is that a wrong statement will be executed. I assume the problem is because the used character set maps some characters. The statement_id is a numeric value and not a string, so it looks very strange to me why this was done. However, i did what i described in "Suggested fix" to solve my problem temporary. A complete reproduction code is given in the "How to repeat" part. Regards ... Joerg Rueschenschmidt How to repeat: // ATTENTION: // // Your database needs to be CHARACTER SET latin1 COLLATE latin1_bin // #include<windows.h> #include <stdlib.h> #include <mysql.h> #define NUM_OF_USED_STMT 97 #define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS t1" #define CREATE_SAMPLE_TABLE "CREATE TABLE t1( a INTEGER ) TYPE = INNODB" #define INSERT_VALUE "INSERT INTO t1 VALUES (%d)" #define SELECT_VALUE "SELECT a FROM t1 WHERE a = %d" void do_test( MYSQL * mysql ) { MYSQL_STMT * stmt[NUM_OF_USED_STMT]; /* used to bind the result */ MYSQL_BIND bind[NUM_OF_USED_STMT]; int int_data[NUM_OF_USED_STMT]; unsigned long length[NUM_OF_USED_STMT]; my_bool is_null[NUM_OF_USED_STMT]; /* some help variables */ int i; char sql_cmd[ 256 ]; /* create test environment */ if( mysql_query(mysql, DROP_SAMPLE_TABLE ) ) { printf("ERR : Drop table failed (%s)\n", mysql_error(mysql)); return; } if( mysql_query(mysql, CREATE_SAMPLE_TABLE ) ) { printf("ERR : Create table error (%s)\n", mysql_error(mysql)); return ; } for ( i = 0; i < NUM_OF_USED_STMT; i++ ) { sprintf( sql_cmd, INSERT_VALUE, i); if( mysql_query(mysql, sql_cmd ) ) { printf("ERR : Insert failed (i = %d) !\n", i ); return ; } } /* init and prepare all statements used in this test */ for ( i = 0; i < NUM_OF_USED_STMT; i++ ) { if ( !(stmt[i] = mysql_stmt_init(mysql) ) ) { printf("ERR : mysql_stmt_init() failed (i=%d, err= %s) !\n", i, mysql_error(mysql)); return ; } sprintf( sql_cmd, SELECT_VALUE, i ); if ( mysql_stmt_prepare( stmt[ i ], sql_cmd, strlen( sql_cmd ) ) ) { printf( "ERR : mysql_stmt_prepare() failed (i=%d, err = %s)\n", i, mysql_error(mysql)); return ; } } /* init the bind array for the results */ for ( i = 0; i < NUM_OF_USED_STMT; i++ ) { bind[i].buffer_type= MYSQL_TYPE_LONG; bind[i].buffer= (char *)&int_data[i]; bind[i].is_null= &is_null[i]; bind[i].length= &length[i]; } /* execute statement and test result */ for ( i = 0; i < NUM_OF_USED_STMT; i++ ) { if ( mysql_stmt_execute( stmt[ i ] ) ) { printf("ERR : mysql_stmt_execute() failed (i=%d, err= %s)\n", i , mysql_error(mysql)); return ; } if ( mysql_stmt_bind_result( stmt[ i ], &bind[i] ) ) { printf("ERR : mysql_stmt_bind_result() failed (i=%d, err= %s)\n", i ,mysql_error(mysql)); return; } while (! mysql_stmt_fetch( stmt[ i ] ) ) { if( i != int_data[ i ] ) { printf("Wrong result in stmt[%d]: (a= %d) should (a= %d)\n", i, int_data[i], i); } } } } main() { MYSQL * mysql = mysql_init( NULL ); if ( ! mysql_real_connect( mysql, /* host */ "localhost", /* user */ "root", /* pwd */ "", /* dbase */ "test", /* uint port */ 0, /* char * socket */ NULL, /* client_flag */ 0 ) ) { printf(" mysql_real_connec() failed\n"); exit(0); } do_test( mysql ); } Suggested fix: Modification in sql_class.cpp : Statement_map::Statement_map() : last_found_statement(0) { enum { START_STMT_HASH_SIZE = 16, START_NAME_HASH_SIZE = 16 }; /* replace default_charset_info with &my_charset_bin now hash_init(&st_hash, default_charset_info, START_STMT_HASH_SIZE, 0, 0, get_statement_id_as_hash_key, delete_statement_as_hash_key, MYF(0)); */ hash_init(&st_hash, &my_charset_bin, START_STMT_HASH_SIZE, 0, 0, get_statement_id_as_hash_key, delete_statement_as_hash_key, MYF(0)); hash_init(&names_hash, &my_charset_bin, START_NAME_HASH_SIZE, 0, 0, (hash_get_key) get_stmt_name_hash_key, NULL,MYF(0)); }