MySQL Bugs: #40439: Error using batched prepared statements

Bug #40439	Error using batched prepared statements
Submitted:	30 Oct 2008 18:07	Modified:	23 Jun 16:34
Reporter:	Stephane Varoqui
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Any
Assigned to:	Bugs System	Target Version:
Tags:	Batch Insert Jdbc
Triage:	D2 (Serious)

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;
	}

Test case

Attachment: TestBug40439.java (text/java), 1.87 KiB.

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)

fix + test

Attachment: bug40439.diff (text/x-diff), 3.05 KiB.

Pushed for release in 5.1.8

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)