MySQL Bugs: #84373: Connector/J:ServerPreparedStatement in loadbalance leads to hanlder SQLException

Bug #84373	Connector/J:ServerPreparedStatement in loadbalance leads to hanlder SQLException
Submitted:	30 Dec 2016 7:03	Modified:	9 Jul 2017 6:47
Reporter:	Haixing Weng (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5-1-39,even 5-1-40	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	loadbalance, ServerPreparedStatement, StatementCache

Description:
Having obtained a connection in configuring load balancing & ServerPreparedstatement & statement cache, and use some PreparedStatements from this connection.
In this pattern, Connector/J will provide a LoadbalanceProxyConnection with multi-host, at first there is only one active physical connection, so ServerPreparedstatement from this connection will only send command to one sever to Pre-compile SQL(that is, called function serverPrepare(..)).But then, I found that if I call commit(or rollback) explicitly on this LoadbalanceProxyConnection, connection will pick a new physicalconnection in its loadbalance strategy(called function picknewconnection())，so that ServerPreparedstatement's connection will be changed probably.When the statement was picked again from cache, it will be executed in other server, and cause an issue like :Unknown prepared statement handler...
This issue is easy to repeat, no matter use connection pool from c3p0,jboss, or use connection from JDBC directly.

How to repeat:
url = "jdbc:mysql:loadbalance://host1,host2,host3/cloudpayment?useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=30&connectTimeout=3000&socketTimeout=3000&loadBalanceBlacklistTimeout=180000&autoReconnect=true

user = xxx
password=xxx

//please mind of those configurations : loadbalance: & useServerPrepStmts=true & cachePrepStmts=true , they are important for issue poc.

con = DriverManager.getConnection(url, userName,
			         password); //or datasource .etc
con.setAutoCommit(false);  //important, to let connector/J pick a new activephysicalconnection when need loadbalance

for (int i = 0; i < 10; i++) //for loadbalance
{
  ps = con.prepareStatement(sql); //prepare same sql
  ps.executeQuery(); //or update
  con.commit();
  ps.close();
}
con.close();

jvm issue report:

java.sql.SQLException: Unknown prepared statement handler (1) given to mysqld_stmt_execute
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)

Suggested fix:
After Connection committing(or rollback) explicitly, statement call function serverPrepare(..) again in new host if found that activephysicalconnection has been changed.

or,

rewrite  loadbalance strategy in a different way, do not pick new connection after explicit commit.

or,
when construct a ServerPreparedstatement instance, send sql to all loadbalance host to pre-compile in one time.

I hope this issue can be handled as soon, I react this poc in most of 5.1.* version of Connector/J.And by the way, this issue cause a greate inconvenience when use c3p0 & hibernate3, or another framework, because in hibernate3, internal execute a statement will always set autocommit in false and commit explicitly.

Hello Haixing Weng,

Thank you for the bug report.
The default load balancing strategy is not intended to be used with cached server-side prepared statement.
your options are below: 
1. stop using server prepared statements or statement cache;
2. implement your own l/b strategy, for example based on query hints.

If you thinks you have a good solution to solve his problem, you can suggest it with a feature request.

Thanks,
Chiranjeevi.

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

Hi Chiranjeevi Battula， I'd like to suggest some solutions with a feature request later, and would you please change status of this report into 'Verified'?

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