Bug #5399 Wrong statement executed by MySQL server
Submitted: 3 Sep 2004 20:08 Modified: 8 Sep 2004 8:35
Reporter: Joerg Rueschenschmidt Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.4-gamma OS:Windows (Windows 2000 / Linux)
Assigned to: Konstantin Osipov CPU Architecture:Any

[3 Sep 2004 20:08] Joerg Rueschenschmidt
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));
}
[8 Sep 2004 0:17] Konstantin Osipov
Thank you Joerg, I applied your fix:
 bk commit - 4.1 tree (konstantin:1.2008) BUG#5399
[8 Sep 2004 8:35] Konstantin Osipov
Fixed in 4.1.5