Maximum of 254 prepared statements per connection

[David.Tymon]

>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_clos e( stmt );
return 0;
}

// Display number of affected rows
printf( "Query affected %d rows\n", mysql_stmt_affe cted_rows( stmt ) );
mysql_stmt_clos e( 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_con nect( 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( "Disconnect ed 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_prep are() 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.000000 000 +1000
+++ mysql-4.1.0-alpha/sql/sql_prepare.cc 2003-07-21 15:51:40.000000 000 +1000
@@ -766,6 +766,17 @@
bzero((char*) &stmt, sizeof(stmt));

stmt.stmt_id= ++thd->current_stmt_i d;
+ /* 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_i d;
+ }
init_sql_alloc( &stmt.mem_ro ot, 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, AustraliaMySQL 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.530C 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=comple x' '--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=Officia l 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/my***********...ie.nctu.edu.tw