Bug #28967 connector/j sometimes send wrong prepared statements to server after failover

• Submitted: 8 Jun 2007 6:48
• Modified: 19 Aug 2007 9:02
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• OS: Any
• CPU Architecture: Any

[8 Jun 2007 6:48] Shane Bester

Description:
In a situation where there are two servers, and C/J is setup for load balancing and failover between them, there is a chance a server could crash due to invalid prepared statements, or valid statements bug invalid parameters being sent.

Occasionally the error is this from the server:

ERROR [00:43:16,606] xServlet - SQLException selecting editdate
java.sql.SQLException: Unknown prepared statement handler (6) given to
mysql_stmt_execute

Query being executed when exception was thrown:

com.mysql.jdbc.ServerPreparedStatement[6] - SELECT x FROM y,z WHERE...
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedState
ment.java:1124)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedSta
tement.java:676)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:103

This means that connector/j is sending trying to execute a stmt_id which it didn't prepare (on this connection at least).

More often, a crash happens and that above error message is not shown.  This implies a correct stmt_id is used, but wrong parameters!  Evidence of parameter corruption can often be seen in mysql server's crash logs.

How to repeat:
Not repeated here yet.

Setup two servers master->master failover.
Setup a multithreaded C/J load tester with connection pool, using prepared statements, and the following connection URL parameters:

&cachePrepStmts=true
&prepStmtCacheSize=100
&prepStmtCacheSqlLimit=512
&connectTimeout=10000
&failOverReadOnly=true
&queriesBeforeRetryMaster=50
&secondsBeforeRetryMaster=30
&cacheServerConfiguration=true
&dontTrackOpenResources=true
&useUnicode=true
&characterEncoding=UTF-8
&characterSetResults=UTF-8
&useCursorFetch=true
&dumpQueriesOnException=true
&blobSendChunkSize=131072

When in high load, shutdown one server using 'mysqladmin shutdown'.  The other server may crash after that.

Suggested fix:
The problem is two-fold.  First is invalid packets possibly sent from c/j to mysql.  Second is mishandling of invalid packets by mysql server.  For mishandling I filed bug #28934

Does c/j guarantee that a prepared statement is only executed on the server that it was prepared on?  Even after fail-over or reconnection?

A workaround is to not use server-side prepared statements.

[12 Jun 2007 5:51] MySQL Verification Team

java version "1.6.0_01"
Java(TM) SE Runtime Environment (build 1.6.0_01-b06)
Java HotSpot(TM) Client VM (build 1.6.0_01-b06, mixed mode, sharing)

5.0.38, 5.0.44..

[10 Jul 2007 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[23 Feb 2009 5:41] Satya Siripuram

java.sql.SQLException: Unknown prepared statement handler (1) given to mysql_stmt_execute
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1169)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)

when we are using the external IP Address then we are not getting the above error, But, when we are using 127.0.0.1 in place of external IP for database we are getting the above error. Is it a mysql bug??.

Thanks & Regards,

Satya Siripuram