Bug #4633 Cannot use Prepared Statement placeholders with LIMIT
Submitted: 19 Jul 2004 19:53 Modified: 30 Sep 2008 9:43
Reporter: Jiho Hahm Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:4.1.4 OS:Any
Assigned to: CPU Architecture:Any

[19 Jul 2004 19:53] Jiho Hahm
Description:
MySQL 4.1.2 alpha
Connector/J 3.1.3 beta
JDK 1.4.2_04
Windows XP

A PreparedStatement query of "SELECT * FROM foo LIMIT ?" fails with the following exception:

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)
	at LimitBug.runTest(LimitBug.java:35)
	at com.mysql.jdbc.util.BaseBugReport.run(BaseBugReport.java:153)
	at LimitBug.main(LimitBug.java:11)
Exception in thread "main"

It works if the LIMIT value is specified directly like in "SELECT * FROM foo LIMIT 0".

The problem does not occur with Connector/J 3.0.14 production.

Using parameters generally seems to work with 3.1.3 beta, so it appears the combination that reproduces the bug is 3.1.3 beta, LIMIT query, and use of parameter as limit value.

How to repeat:
// the reproducible test case

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.mysql.jdbc.util.BaseBugReport;

public class LimitBug extends BaseBugReport {

	public static void main(String[] args) throws Exception {
		LimitBug bug = new LimitBug();
		bug.run();
	}
	
	public void setUp() throws Exception {
		Connection conn = getConnection();
		PreparedStatement stmt =
			conn.prepareStatement("CREATE TABLE foo (col VARCHAR(10))");
		stmt.executeUpdate();
		stmt.close();
	}

	public void tearDown() throws Exception {
		Connection conn = getConnection();
		PreparedStatement stmt =
			conn.prepareStatement("DROP TABLE foo");
		stmt.executeUpdate();
		stmt.close();
	}

	public void runTest() throws Exception {
		int limit = 0;
		Connection conn = getConnection();
		PreparedStatement stmt =
			conn.prepareStatement("SELECT * FROM foo LIMIT ?");
		stmt.setInt(1, limit);
		ResultSet rs = stmt.executeQuery();
		rs.close();
		stmt.close();
	}

	public String getUrl() {
		return "jdbc:mysql://127.0.0.1/test";
	}
}
[20 Jul 2004 3:52] Mark Matthews
This isn't really a JDBC issue, the server itself does not support placeholders for LIMIT (currently) when using server-side prepared statements.

Until this is fixed, you should disable server-side prepared statements in the JDBC driver by using the 'useServerPrepStmts=false' property in your connection URL.
[20 Jul 2004 4:15] Jiho Hahm
But it worked when Connector/J 3.0.14 was used.  Are different versions of Connector/J making different requests to the server for the same query?
[26 Jul 2004 19:11] Dean Ellis
Connector/J 3.0 did not use server-side prepared statements.
[26 Jul 2004 20:19] Dean Ellis
Verifying so we can track when this is corrected.
[27 Jul 2004 8:05] Chris Wood
I have a fix for the java connector, it checks select statements to determine if they have parameterized LIMIT criteria, and falls back to client side prepared statements when this is the case. 

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, I suggest this test is done in the "initializePropsFromServer" function, and a boolean variable set to determine if the check is neccicary.

In any case, my fix is attached to bug #4718, It's pretty self contained and has no impact outside of two methods, so I believe it would be a good inclusion into the Java connector codebase.
[5 Feb 2005 3:45] Jens Elkner
When does the bug get fixed. It is almost a half year old and nothing seems to be happen?

Actually, I need to make a long term decision, what DB to use for our future
projects. I thought on MySQL, but investigating a little bit deeper, I have the
feeling, that this would probably not a good idea, since long outstanding bugs,
which have a really big influence on applications (1), do not get fixed.

I'm not sure, but I guess, AS developers will not change all their code, just
because MySQL is not willingly (or able?) to fix the server. The most developers 
I've talked to just said "Well, just take a real DB ..." :( Hmmm, but the white
papers about mysql are promising. So ...

(1) just do a google on "mysql server prepared statements" and you'll see,
how many people are struggling with this bug ...
[5 Feb 2005 6:26] 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.
[30 Sep 2008 9:42] Konstantin Osipov
The issue was fixed in the server in 5.0.