Bug #9465 Syntax error when using a prepared statement and limit
Submitted: 29 Mar 2005 19:41 Modified: 29 Mar 2005 21:59
Reporter: Joris Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.6 / 3.2.0-alpha OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[29 Mar 2005 19:41] Joris
Description:
The problem occurs when using the connector/j libraries version 3.1.6 or 3.2.0-alpha
but version 3.0.14 works fine.

Environment:
client (Servlets) deployed on Apache Tomcat version 5.0.27 running on Windows XP profesional
mysql version 4.1.10-nt-max running on Windows XP  (on a different computer)

The table in question (link) is using InnoDB 

The problem:
Creating a prepared statement with a ? as the last character in the limit clause causes an exception (Syntax error in the SQL statement)

The line (LinkHandler.java:185) that causes the problem (con is the connection to the database):
PreparedStatement ps = con.prepareStatement("SELECT linkid FROM link LIMIT 0 , ?");

The stacktrace:
java.sql.SQLException: 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:2851)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1534)
	at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.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)
	at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:185)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
	at startpagina.dbhandler.LinkHandler.getTopX(LinkHandler.java:185)

How to repeat:
create a simple table using InnoDB and either a 3.1.x or 3.2.x version of the connector/j library and try to create the prepared statement mentioned above.
[29 Mar 2005 21:59] Miguel Solorzano
Duplicate of:

http://bugs.mysql.com/bug.php?id=4633d
[9 Aug 2005 16:19] 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/28074