473,378 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Maximum of 254 prepared statements per connection

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.
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;

runQuery( MYSQL* mysql, const char* sql )
MYSQL_STMT* stmt = 0;


// 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",
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;

main( int argc, char* argv[] )
const char* sql = "INSERT INTO dummy VALUES ( 'Hello' )";
MYSQL* mysql = 0;
int i = 0;


mysql = mysql_init( 0 );
if ( !mysql )
fprintf( stderr, "mysql_init failed\n" );
return( 1 );

// Connect to database
if ( !mysql_real_connect( mysql,
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 );

// Disconnect from database
mysql_close( mysql );
printf( "Disconnected from database\n" );

return( 0 );
After doing some investigation the problem seems to be with the MySQL
protocol. The server response to a mysql_prepare() is:

[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>
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)

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=''
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/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 2984

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Mark | last post by:
hello! mysqli in PHP5 comes with prepared statements functionality. However, without persistent connections or connection pooling in this code library, one has to ask: why bother? are...
by: Deepali Gupta | last post by:
We are encountering the problem of "maximum open cursors exceeded" exception. Our web based system uses jdk1.3 as frontend with Oracle 9i as backend and Oracle JDBC Driver version -
by: Tom D | last post by:
I'm rewriting a database interface that our company currently has. Currently it's using the Pear::DB interface, but we found that that was introducing a bit too much overhead. I'm rewriting the...
by: Prashanth | last post by:
I am getting this error from BEA DB2 Driver in weblogic console. java.sql.SQLException: CURSOR C02 NOT IN A PREPARED STATE java.sql.SQLException: CURSOR C02 NOT IN A PREPARED STATE We keep...
by: Kums | last post by:
What is the maximum permissible size of a database? Is there any limitation. What is the maximum # of tablespace's allowed in a database? Thanks for your response.
by: NS | last post by:
Hi, I am trying to execute a prepare statement using oledb provider for DB2. The command.Prepare() statement is giving me an exception " No error information available:...
by: birju | last post by:
Hi, I'm running SQL Profiler on an SQL Server 2000 database. I see that one stored procedure gets repeatedly executed having a handle of '1'. This query takes a long time to complete. How do I...
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected...
by: ojorus | last post by:
Hi! Some questions regarding the mysqli-extension (php5) 1) Prepared statements: If I understand things right, prepared statements will give better performance if you make several similar...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.