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: | |
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
[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.