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:
Category:Connector / J Severity:S2 (Serious)
Version:3.1.3-beta OS:
Assigned to: Mark Matthews CPU Architecture:Any

[23 Jul 2004 10:06] Chris Wood
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.
[23 Jul 2004 10:06] Chris Wood
Test case that demonstrates the issue.

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

[23 Jul 2004 10:07] Chris Wood
Fixed Connection class.

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

[26 Jul 2004 19:15] Dean Ellis
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.
[30 Jul 2004 17:23] Sergei Golubchik
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
[30 Jul 2004 18:14] Mark Matthews
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.
[31 Jul 2004 4:19] Chris Wood
Maybe the connector should perform it's version check and disable server side prepared statements until server side prepared statements work without any issues?
[7 Jan 2005 13:56] Michael Bartmann
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 :-(

[5 Feb 2005 6:25] Mark Matthews
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.