Unknown prepared statement handler (X) given to mysqld_stmt_execute

[Ian Clark]

Hi,

I have a java application calling percona mysql through a generic data access library.

Every so often calls will fail with this error:-

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE "Connector" SET "field_status"=?,"field_lastCheckedAt"=? WHERE "id"= ?]; SQL state [HY000]; error code [1243]; Unknown prepared statement handler (1094996053) given to mysqld_stmt_execute; nested exception is java.sql.SQLException: Unknown prepared statement handler (1094996053) given to mysqld_stmt_execute
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:875) ~[or

It only seems to happen on my production environment which is setup in a master<->master percona cluster, there is also an AWS elb between the java application and the mysql. This error doesn't happen in a development environment single app server, single db node, no elb.

Currently the jdbc connection string is (new lines added for clarity):-

jdbc:mysql://mysqlserver:3306/thedb?user=user&
password=password&
cacheServerConfiguration=true&
elideSetAutoCommits=true&
alwaysSendSetIsolation=false&
enableQueryTimeouts=false&
cacheResultSetMetadata=true&
useUnicode=true&
characterEncoding=UTF-8&
cachePrepStmts=false&
connectionCollation=utf8_general_ci&
useServerPrepStmts=true&
prepStmtCacheSqlLimit=1000&
rewriteBatchedStatements=true&
useLocalSessionState=true&
useLocalTransactionState=true&
tcpKeepAlive=true&
tcpTrafficClass=24&
allowMultiQueries=true&
maintainTimeStats=false

In the app server I am pooling connection using the C3P0 library, setup like so:-

dataSource = new ComboPooledDataSource();
    dataSource.setJdbcUrl(jdbcUrl);
    dataSource.setDriverClass(driverClass == null ? null : driverClass.getName());
    dataSource.setPreferredTestQuery("select 1");
    dataSource.setIdleConnectionTestPeriod(30);
    dataSource.setTestConnectionOnCheckin(true);
    dataSource.setMaxPoolSize(200);
    dataSource.setMinPoolSize(50);
    dataSource.setMaxStatementsPerConnection(0);

It doesn't happen consistently and the id is always 1094996053, even after all the app servers have been restarted!

What could the cause be?

Any help much appreciated.

Ian Clark

[Bill Karwin]

On Dec 19, 2013, at 3:04 AM, Ian Clark wrote:

Hi,

I have a java application calling percona mysql through a generic data access library.

Every so often calls will fail with this error:-

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE "Connector" SET "field_status"=?,"field_lastCheckedAt"=? WHERE "id"= ?]; SQL state [HY000]; error code [1243]; Unknown prepared statement handler (1094996053) given to mysqld_stmt_execute; nested exception is java.sql.SQLException: Unknown prepared statement handler (1094996053) given to mysqld_stmt_execute
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) ~[org.springframework.jdbc_3.0.3.RELEASE.jar:3.0.3.RELEASE]

I notice you're using double-quotes to delimit your table and column names. This is standard SQL, but it's not the default identifier delimiter that MySQL uses. The default meaning of double-quotes is identical to single-quotes, which is a delimiter for string literals or date literals.

One can enable double-quotes as identifier delimiters by setting SQL_MODE=ANSI or SQL_MODE=ANSI_QUOTES.

If you don't have that SQL mode set correctly, a statement like UPDATE "Connector" ... should fail and throw an exception, because it makes no sense to UPDATE a string literal like that.

It only seems to happen on my production environment which is setup in a master<->master percona cluster

The fact that it works in one MySQL instance and doesn't work in the other instance would be expected if there's a difference in the global SQL mode configured on each instance.

So I recommend you check what your current SQL_MODE is set to on the development server and the production server, and see if one sets that mode and the other doesn't. You can check this with either of the following statements:

mysql> show global variables like 'sql_mode';

or

mysql> select @@sql_mode;

Also consider that an individual session can override the global SQL mode. This would be harder to track down, but it'd be likely to be something your application executes deliberately.

The MySQL JDBC connector is supposed to automatically adapt if you set ANSI_QUOTES mode, but there are probably ways application code can confuse that automatic behavior.

Regards,

Bill Karwin

[Ian Clark]

Hi Bill,

I checked both of our servers, and they are the same:-

+---------------+-------------+

| Variable_name | Value       |

+---------------+-------------+

| sql_mode      | ANSI_QUOTES |

+---------------+-------------+

(We specifically set this in our my.cnf)

The problem is strange, I guess either the server or the jdbc connection is caching the statement despite us turning these off. The fact it is always the same id 1094996053 is really suspicious but really out of ideas as to the cause.

Ian Clark