Issues with server-side prepared statements in MySQL Connector/ODBC 5.2.2

Question

Background

I got a report from a customer who complained that they got the following error message in their log files after performing some updates on their server:

ERROR [HY000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.1.49]Can't create more than max_prepared_stmt_count statements (current value: 16382)

My findings

Google gave me the following interesting result.

After reading the above blog post I started to experiment, and sure enough - when updating to the new ODBC driver (5.2.2, aka "MySQL ODBC 5.2w Driver") all statements are now server-side prepared by default. (also see the announcement here).

Here's two lines from the global log using 5.2.2:

[192.168.0.150]|134302|0|Prepare|INSERT INTO `a1gt6` (`TimeStamp`, `Temperature`) VALUES (?, ? )
[192.168.0.150]|134302|0|Execute|INSERT INTO `a1gt6` (`TimeStamp`, `Temperature`) VALUES ('2012-11-25 12:40:27', '7.03750000000000000e+001' )

The problem

I wouldn't have a big issue with my statements being turned into prepared statements if it wasn't for the fact that they apparently are not closed by the server (in a timely manner, see update below), as indicated by the above error message and the result from the command show global status like "com_stmt%";

Com_stmt_close  0
Com_stmt_execute    1
Com_stmt_fetch  0
Com_stmt_prepare    1
Com_stmt_reprepare  0
Com_stmt_reset  0
Com_stmt_send_long_data 0

The above table shows the result after running a statement a single time using Connector/ODBC 5.2.2. Below is the output from show global status like "com_stmt%"; using Connector/ODBC/5.1.11

Com_stmt_close  0
Com_stmt_execute    0
Com_stmt_fetch  0
Com_stmt_prepare    0
Com_stmt_reprepare  0
Com_stmt_reset  0
Com_stmt_send_long_data 0

Below is the C# test method I've been using to determine the cause of the problem.

[TestMethod]
public void TestOldfashion()
{
    int option = 16 + /*FLAG_MULTI_STATEMENTS*/ 67108864;
    string odbcString = @"DRIVER={{{0}}};SERVER={1};UID={2};PWD={3};OPTION=" + option + ";CharSet=utf8;";
    using( System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection( string.Format( odbcString, new object[] { "MySQL ODBC 5.1 Driver", "server", "user", "password" } ) ) ) {
        con.Open();
        con.ChangeDatabase( "fooDB" );
        using( System.Data.Odbc.OdbcCommand cmd = con.CreateCommand() ) {
            cmd.CommandText = "INSERT INTO myTable ( foo, value ) VALUES ( ?, ? );";
            cmd.Parameters.AddWithValue( "foo", 2 );
            cmd.Parameters.AddWithValue( "value", "asf" );
            int i = cmd.ExecuteNonQuery();
        }
    }
}

And this is the CREATE statement for the table:

delimiter $$

CREATE TABLE `mytable` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `foo` float DEFAULT NULL,
  `value` text,
  PRIMARY KEY (`index`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8$$

Pre-posting update

Just before posing this, I executed the show global status like "com_stmt%"; again and now it gives the following result, so it appears that the server does clean up the statements, but much too late (considering the above error message). The last one remains unclosed( in Com_stmt_reset) even after 30 minutes.

Com_stmt_close  6
Com_stmt_execute    7
Com_stmt_fetch  0
Com_stmt_prepare    7
Com_stmt_reprepare  0
Com_stmt_reset  1
Com_stmt_send_long_data 0

The announcement mentions that it is possible to disable the server-side prepare by adding the no_ssps=1 option to the connection string. This seems to work (only briefly tested it) but it does not feel like a solid solution. I'd rather use the defaults as much as possible as I expect the MySQL devs knows what the best practices are. Likely I'm doing something wrong or not understanding things properly.

The questions

If the server now turns my regular statement into a prepared statement (as opposed to the old client-side emulation), why doesn't it clean it up in a timely manner so that the above error is avoided?

I'd expect the statement to be closed/released when the end of the using-scope for the OdbcCommand is reached, but that is apparently incorrect?

I'd much appreciate if someone could shed some light on this. Unfortunately it is 00:55 so I can't stay up to answer/clarify things now, but will check in tomorrow morning again.

Answer 1

This is a known bug to which a patch has been submitted and approved. You can:

• wait for a fixed release;
• apply the patch yourself and recompile;
• downgrade to 5.1; or
• use no_ssps=1.