[CONJ-168] Rewritten batch insert can fail if the first value isn't a parameter Created: 2015-07-21  Updated: 2015-09-02  Resolved: 2015-07-22

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 1.1.9
Fix Version/s: 1.2.2

Type: Bug Priority: Major
Reporter: Brendan Behan Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None

Sprint: Sprint connector/j 1.3.0

 Description   

When performing a batch insert with the rewriteBatchedStatements=true option, the insert can fail if the first value being inserted isn't a parameter. The following test case demonstrates the problem:

@Test
 
public void testInsertWithLeadingConstantValue() throws Exception {
 
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true", "root", "root");
 
    Statement stmt = null;
 
    PreparedStatement insertStmt = null;
 
    try {
 
        stmt = conn.createStatement();
 
        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS test_table(col1 VARCHAR(32), col2 VARCHAR(32))");
 
        
        insertStmt = conn.prepareStatement("INSERT INTO test_table (col1, col2) values('some value', ?)");
 
        insertStmt.setString(1, "a");
 
        insertStmt.addBatch();
 
        insertStmt.setString(1, "b");
 
        insertStmt.addBatch();
 
        insertStmt.executeBatch();
 
        conn.commit();
 
    }
 
    finally {
 
        if (insertStmt != null) {
 
            insertStmt.close();
 
        }
 
        if (stmt != null) {
 
            stmt.close();
 
        }
 
        conn.close();
 
    }
 
}

The insert attempt results in the following SQL being executed:

INSERT INTO test_table (col1, col2) values('some value', 'a'),('b')

The SQL fails with the following error:

java.sql.BatchUpdateException: Column count doesn't match value count at row 2
 
	at org.mariadb.jdbc.MySQLStatement.executeBatch(MySQLStatement.java:1291)
 
	at org.mariadb.jdbc.TestBatchInsert.testInsertWithLeadingConstantValue(TestBatchInsert.java:24)
 
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
 
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 
	at java.lang.reflect.Method.invoke(Method.java:606)
 
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
 
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
 
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
 
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
 
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
 
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
 
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
 
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:44)
 
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:180)
 
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:41)
 
	at org.junit.runners.ParentRunner$1.evaluate(ParentRunner.java:173)
 
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
 
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
 
	at org.junit.runners.ParentRunner.run(ParentRunner.java:220)
 
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
 
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
 
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
 
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
 
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
 
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
 
Caused by: java.sql.SQLException: Column count doesn't match value count at row 2
 
	at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:149)
 
	at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
 
	at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:268)
 
	at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:335)
 
	at org.mariadb.jdbc.MySQLStatement.executeBatch(MySQLStatement.java:1271)
 
	... 25 more
 
Caused by: org.mariadb.jdbc.internal.common.QueryException: Column count doesn't match value count at row 2
 
	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:995)
 
	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1050)
 
	at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:328)
 
	... 26 more

This seems to be because the initial part of the values clause up to the first parameter isn't being included in the rewritten SQL. The following patch addresses this issue:

diff --git a/src/main/java/org/mariadb/jdbc/internal/common/query/MySQLParameterizedQuery.java b/src/main/java/org/mariadb/jdbc/internal/common/query/MySQLParameterizedQuery.java
 
index 364a185..8f7dd5a 100644
 
--- a/src/main/java/org/mariadb/jdbc/internal/common/query/MySQLParameterizedQuery.java
 
+++ b/src/main/java/org/mariadb/jdbc/internal/common/query/MySQLParameterizedQuery.java
 
@@ -139,6 +139,9 @@
 
             throw new AssertionError("Invalid query, queryParts was empty");
 
         }
 
         os.write(",(".getBytes());
 
+        if (queryPartsArray[0].length > rewriteOffset) {
 
+        	os.write(queryPartsArray[0], rewriteOffset + 1, queryPartsArray[0].length - rewriteOffset - 1);
 
+        }
 
         for(int i = 1; i<queryPartsArray.length; i++) {
 
             parameters[i-1].writeTo(os);
 
             if(queryPartsArray[i].length != 0)



 Comments   
Comment by Diego Dupin [ 2015-07-21 ]

there will be the same issue if there is a parameter after .
test must be like

    stmt.executeUpdate("CREATE TABLE IF NOT EXISTS test_table(col1 VARCHAR(32), col2 VARCHAR(32), col3 VARCHAR(32))");
 
    PreparedStatement insertStmt = conn.prepareStatement("INSERT INTO test_table (col1, col2, col3) values('some value', ?, 'other value')");

Generated at Thu Feb 08 03:13:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.