Bug #7306 the server side preparedStatement error for LIMIT placeholder

Submitted: 15 Dec 2004 5:42 Modified: 10 Jun 2005 18:33
Reporter: Li wy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:Windows (window 2000)
Assigned to: Konstantin Osipov CPU Architecture:Any

[15 Dec 2004 5:42] Li wy
Description:
I use Connector/J 3.1.5 to access the mysql database, and run the follow java code:

String sql = "select * from sometable LIMIT ?,?";
PreparedStatement stmt = conn.prepareStatement(sql);

Then the follow exception is thowed:
java.sql.SQLException: You have an error in your SQL syntax; check the manual th
at corresponds to your MySQL server version for the right syntax to use near '?,
?' at line 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2849)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
        at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedSt
atement.java:1485)
        at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement
.java:151)
        at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1309)
        at com.mysql.jdbc.Connection.prepareStatement(Connection.java:1281)

How to repeat:
Just run 

String sql = "select * from sometable LIMIT ?,?";
PreparedStatement stmt = conn.prepareStatement(sql);

Suggested fix:
Should fix ServerPreparedStatement
[5 Feb 2005 6:34] Mark Matthews
This is a dupe of #4718

Since this isn't going to be fixed in the server "real-soon-now", I've patched the JDBC driver to cope:

"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."
[17 Feb 2005 22:54] Konstantin Osipov
Setting version to 5.0 as it will be fixed there.
[29 May 2005 20:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25373
[31 May 2005 16:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25414
[1 Jun 2005 13:10] Michael Widenius
First part approved and pushed. Waiting for next part
[4 Jun 2005 12:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25610
[5 Jun 2005 17:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25619
[7 Jun 2005 12:41] Konstantin Osipov
Fixed in 5.0 tree marked 5.0.7
[7 Jun 2005 20:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25730
[10 Jun 2005 18:33] Paul DuBois
Noted in 5.0.7 changelog.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.060 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.