>Description: MySQL v4.1.0-alpha only allows a client to prepare a maximum of 254 statements. On the 255th mysql_prepare() call, a failure is returned with no information returned by mysql_error(). This occurs even if the statements are closed after each use.
>How-To-Repeat: The following program highlights the problem. Just change the connection details to some database. It also assumes there is a table called dummy, created with "CREATE TABLE dummy ( a VARCHAR(1024) )" It was compiled using: gcc -o buggy buggy.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient -lz #include <mysql.h> #include <stdio.h> #include <string.h> int queryCount = 0; int runQuery( MYSQL* mysql, const char* sql ) { MYSQL_STMT* stmt = 0; ++queryCount; // Prepare statement stmt = mysql_prepare( mysql, sql, strlen( sql ) ); if ( !stmt ) { fprintf( stderr, "Failed to prepare statement: %s\n", sql ); fprintf( stderr, "%s\n", mysql_error( mysql ) ); return 0; } printf( "Query number %d has statement id %u\n", queryCount, stmt->stmt_id ); // Execute statement if ( mysql_execute( stmt ) != 0 ) { fprintf( stderr, "Failed to execute prepared statement: %s\n", sql ); mysql_stmt_close( stmt ); return 0; } // Display number of affected rows printf( "Query affected %d rows\n", mysql_stmt_affected_rows( stmt ) ); mysql_stmt_close( stmt ); return 1; } int main( int argc, char* argv[] ) { const char* sql = "INSERT INTO dummy VALUES ( 'Hello' )"; MYSQL* mysql = 0; int i = 0; my_init(); mysql = mysql_init( 0 ); if ( !mysql ) { fprintf( stderr, "mysql_init failed\n" ); return( 1 ); } // Connect to database if ( !mysql_real_connect( mysql, "localhost", "user", "password", "testDB", 0, "/var/lib/mysql/mysql.sock", 0 ) ) { fprintf( stderr, "Failed to connect to MySQL database\n" ); fprintf( stderr, "%s\n", mysql_error( mysql ) ); mysql_close( mysql ); return( 1 ); } printf( "Connected to database\n" ); // Table dummy is just declared as: // // CREATE TABLE dummy ( a VARCHAR(1024) ) // for ( i = 0; i < 3000; ++i ) { if ( !runQuery( mysql, sql ) ) { fprintf( stderr, "Failed to run query: %s\n", sql ); break; } } // Disconnect from database mysql_close( mysql ); printf( "Disconnected from database\n" ); return( 0 ); } >Fix: After doing some investigation the problem seems to be with the MySQL protocol. The server response to a mysql_prepare() is: [STMT_ID:4] [Column_count:2] [Param_count:2] [Columns meta info] (if Column_count > 0) [Params meta info] (if Param_count > 0 ) (TODO : 4.1.1) So, once the statement id reaches 255, the first byte in the response sent by the server is 255(0xff). However in net_safe_read() in libmysql/libmysql.c and libmysql_r/libmysql.c there is the following test: if (net->read_pos[0] == 255) { <report an error> } which in this case is not true. As a workaround, I changed mysql_stmt_prepare() in sql/sql_prepare.cc using the following patch: diff -r -u mysql-4.1.0-alpha.orig/sql/sql_prepare.cc mysql-4.1.0-alpha/sql/sql_prepare.cc --- mysql-4.1.0-alpha.orig/sql/sql_prepare.cc 2003-04-03 22:15:51.000000000 +1000 +++ mysql-4.1.0-alpha/sql/sql_prepare.cc 2003-07-21 15:51:40.000000000 +1000 @@ -766,6 +766,17 @@ bzero((char*) &stmt, sizeof(stmt)); stmt.stmt_id= ++thd->current_stmt_id; + /* Seems to be a bug in the MySQL protocol here. If last byte of + * stmt_id == 0xff then the client interprets this is an error. + * Refer to net_safe_read() in libmysql.c and notice how it checks + * if the first byte is 255. If so then it is an error. + * So just make sure that statement id cannot have 0xff as it's last + * byte. + */ + while ( ( stmt.stmt_id & 0xff ) == 0xff ) + { + stmt.stmt_id= ++thd->current_stmt_id; + } init_sql_alloc(&stmt.mem_root, 8192, 8192); stmt.thd= thd; So just ensuring the statement id never has 0xff in the bottom byte. >Submitter-Id: <submitter ID> >Originator: >Organization: Safehouse International, Melbourne, Australia >MySQL support: none >Synopsis: Maximum of 254 prepared statements per connection. >Severity: critical >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-4.1.0-alpha (Official MySQL RPM) >Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for pc-linux on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.1.0-alpha-Max Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 3 min 5 sec Threads: 1 Questions: 9903 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 53.530 >C compiler: gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) >C++ compiler: g++ (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) >Environment: System: Linux entrails.como 2.4.20-13.8.sh1smp #1 SMP Wed Jun 25 14:35:30 EST 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O2 -march=i686' CXX='g++' CXXFLAGS='-O2 -march=i686 -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 16 17:50 /lib/libc.so.6 -> libc-2.3.2.so -rwxr-xr-x 1 root root 1292588 Apr 8 05:44 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2323252 Apr 8 05:03 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 8 04:54 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O2 -march=i686' 'CXXFLAGS=-O2 -march=i686 -felide-constructors -fno-exceptions -fno-rtti ' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]