MySQL Bugs: #69077: Server-side PreparedStatement restrictions don't reflect current capabilities

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:	Assigned Account	CPU Architecture:	Any

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.

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)

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.