>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]
