MySQL Bugs: #4718: Prepared statements with LIMIT criteria fail when the limit is a parameter.

Bug #4718	Prepared statements with LIMIT criteria fail when the limit is a parameter.
Submitted:	23 Jul 2004 10:06	Modified:	5 Feb 2005 6:25
Reporter:	Chris Wood	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.3-beta	OS:
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
Parameterized LIMIT select statements fail to compile as server side prepared statements.

How to repeat:
conn.createPreparedStatement("SELECT * from test_table LIMIT ?");

You get:

java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1"
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2746)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1532)
	at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1431)
	at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:144)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1288)
	at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1260)

Suggested fix:
Update Connection so that LIMIT prepared statments do not get executed as server side prepared statments.

A workaround is to use "useServerPrepStmts=false" in the connection URL, however this disables all server side prepared statements so is undesirable.

I've attached a modified Connection class, and a test case which replicates the issue.

The modified Connection class adds a method "checkServerPreparedStatement" which tests for a parameterized limit statement, and disables server side prepared statments in this case. The other modification is in the "prepareStatement" method, to call the check method.

In future the DB may fix prepared statements with parameterized LIMITs, in this case the server version should be tested for in the check method.

Test case that demonstrates the issue.

Attachment: PreparedStatementWithLimitTest.java (text/plain), 2.50 KiB.

Fixed Connection class.

Attachment: Connection.java (text/plain), 151.70 KiB.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

Duplicate of report # 4633.

no, let's not consider it duplicate but a separate Connector/J related issue.
And BUG#4633 will be a server's feature request.

Mark - note that there's a patch attached

We've decided to wait for the server fix on this, as the GA version of MySQL-4.1 won't have this issue. 

We usually try and avoid adding features to the clients that will _soon_ be fixed by functionality in the server, especially given the fact that for client-side consistency, this fix would need to be replicated to Connector/Net, libmysql and Connector/ODBC.

Maybe the connector should perform it's version check and disable server side prepared statements until server side prepared statements work without any issues?

Is there any progress with this issue?

Hibernate uses "limit ?" with its MySQLDialect under certain circumstances
and no workaround is planned, but assumed on the mysql side.
Mysql connector is not fixed, but fix assumed on server side.
(connector workaround/fix exists, but is not incorporated into release)
Former comment on this issue states that the server would get fixed in 4.1 GA,
but that is what I am using, to no avail.

I tried the 3.2.0 alpha connector: to no avail.
Perhaps the 5.0 alpha server does the job?

I apologize if I am missing something, but I fear simply nothing happened
for the last half year :-(

Regards,
Michael

The community has spoken, and has not been able to wait for a server fix, 

So, by default, the driver now scans SQL you are preparing via all variants
      of Connection.prepareStatement() to determine if it is a supported 
      type of statement to prepare on the server side, and if it is not 
      supported by the server, it instead prepares it as a client-side emulated
      prepared statement (BUG#4718). You can disable this by passing 
      'emulateUnsupportedPstmts=false' in your JDBC URL.

This will ship in Connector/J 3.1.7, and 3.2.0. Nightly snapshots of 3.1.7 with the fix applied will be available after 00:00 GMT tomorrow, February 7th for testing from http://downloads.mysql.com/snapshots.php.

Thanks for your patience.