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

Reply via email to