| Bug #40439 | Error using batched prepared statements | ||
|---|---|---|---|
| Submitted: | 30 Oct 2008 17:07 | Modified: | 23 Jun 2009 14:34 |
| Reporter: | Stephane Varoqui | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.7 | OS: | Any |
| Assigned to: | Jess Balint | CPU Architecture: | Any |
| Tags: | Batch Insert Jdbc | ||
[30 Oct 2008 20:34]
Tonci Grgin
Test case
Attachment: TestBug40439.java (text/java), 1.87 KiB.
[30 Oct 2008 20:38]
Tonci Grgin
Hello Stephane.
Verified as described using latest c/J sources and attached test case. If we do not use "rewriteBatchedStatements" everything is just fine. When "rewriteBatchedStatements=true" we have an error:
Connected to 5.0.68-pb10-log
java.vm.version : 1.5.0_12-b04
java.vm.vendor : Sun Microsystems Inc.
java.runtime.version : 1.5.0_12-b04
os.name : Windows XP
os.version : null
sun.management.compiler : HotSpot Client Compiler
E
Time: 0,407
There was 1 error:
1) testBug40439(testsuite.simple.TestBug40439)java.sql.BatchUpdateException: 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 'values (2,'toto',2),(id,data, ordr) values (3,'toto',3),(id,data, ordr) values (' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1495)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1097)
at testsuite.simple.TestBug40439.testBug40439(TestBug40439.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at testsuite.simple.TestBug40439.main(TestBug40439.java:57)
FAILURES!!!
Tests run: 1, Failures: 0, Errors: 1
And on server:
61 Query INSERT INTO bug40439_values (id,data, ordr) values (1,'toto',1),(id,data, ordr) values (2,'toto',2),(id,data, ordr) values (3,'toto',3),(id,data, ordr) values (4,'toto',4),(id,data, ordr) values (5,'toto',5),(id,data, ordr) values (6,'toto',6),(id,data, ordr) values (7,'toto',7),(id,data, ordr) values (8,'toto',8),(id,data, ordr) values (9,'toto',9),(id,data, ordr) values (10,'toto',10)
[2 Mar 2009 21:45]
Jess Balint
fix + test
Attachment: bug40439.diff (text/x-diff), 3.05 KiB.
[2 Jun 2009 5:59]
Jess Balint
Pushed for release in 5.1.8
[23 Jun 2009 14:34]
Tony Bedford
An entry was added to the 5.1.8 changelog:
When using rewriteBatchedStatements=true with:
INSERT INTO table_name_values (...) VALUES (...)
Query rewriting failed because “values” at the end of the table name was mistaken for the reserved keyword. The error generated was as follows:
testBug40439(testsuite.simple.TestBug40439)java.sql.BatchUpdateException: 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 'values (2,'toto',2),(id,data, ordr) values
(3,'toto',3),(id,data, ordr) values (' at line 1
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1495)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1097)
at testsuite.simple.TestBug40439.testBug40439(TestBug40439.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at testsuite.simple.TestBug40439.main(TestBug40439.java:57)
Description: When using rewriteBatchedStatements=true with: insert into table_name_values (...) values (...) Query rewriting will fail because 'values' at the end of the table name is mistaken for the reserved keyword. How to repeat: conn.setAutoCommit(false); final PreparedStatement ps = conn .prepareStatement("INSERT INTO PRIMI_VALUES (ID, ELEMENT, ORDR) VALUES (?, ?, ?)"); for (int k = 0; k < 10; k++) { ps.setInt(1, k); ps.setString(2, "toto"); ps.setInt(3, k); ps.addBatch(); } conn.commit(); Table name must be terminated by "VALUES" and followed by an empty space Suggested fix: final static Pattern REGEXP = Pattern.compile("^.*?[ \\)]VALUES *\\(", Pattern.CASE_INSENSITIVE); private String extractValuesClause() throws SQLException { if (this.batchedValuesClause == null) { final Matcher matcher = REGEXP.matcher(this.originalSql); if (!matcher.find()) { return null; } int indexOfFirstParen = matcher.group().length()-1; assert indexOfFirstParen >= "INSERT INTO x VALUES(".length()-1; assert this.originalSql.charAt(indexOfFirstParen) == '('; if (indexOfFirstParen == -1) { return null; } int indexOfLastParen = this.originalSql.lastIndexOf(')'); if (indexOfLastParen == -1) { return null; } this.batchedValuesClause = this.originalSql.substring(indexOfFirstParen, indexOfLastParen + 1); } return this.batchedValuesClause; }