Bug #69077 Server-side PreparedStatement restrictions don't reflect current capabilities
Submitted: 26 Apr 2013 5:43
Reporter: Todd Farmer (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.24 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any

[26 Apr 2013 5:43] Todd Farmer
Description:
Connector/J checks the SQL statement to ensure it can be processed as a server-side prepared statement, but this check has not been kept current.  Among the restrictions:

		if (StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "CALL")) {
			return false;
		}
...

[check for using placeholders in LIMIT clause with MySQL 5.0.6 or earlier]
...

		} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "CREATE TABLE")) {
			canHandleAsStatement = false;
		} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "DO")) {
			canHandleAsStatement = false;
		} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "SET")) {
			canHandleAsStatement = false;
		}

According to MySQL 5.6 documentation, all of the above can be used in prepared statements:

http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html

How to repeat:
See above.

Try to create a server-side prepared statement for SET command.

Suggested fix:
Update (or remove) restrictions related to server-side prepared statement processing.
[6 May 2013 21:36] Jess Balint
According to the docs,
DO, SET, CREATE TABLE are supported in SSPS going back to 4.1, and CALL since 5.0 (when SPs were introduced)
[7 May 2013 23:17] Todd Farmer
Changing canHandleAsServerPreparedStatementNoCache() to always return true produces no meaningful regressions in StatementRegressionTest.  This is because emulateUnsupportedPstmts defaults to true, which handles *any* SQLException in the attempt to prepare a statement (not just error 1295) as a  trigger to fail to emulated, client-side prepared statements.