Bug #110323 allowMultiQueries=true got error in SQL syntax
Submitted: 9 Mar 2023 11:46 Modified: 10 Mar 2023 8:35
Reporter: Wenqian Deng Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[9 Mar 2023 11:46] Wenqian Deng
Description:
When I set allowMultiQueries to true and use executeBatch with correctly formatted SQL statements, I received an error message:

java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';INSERT INTO t0 VALUES (-1)' at line 1
	at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:484)
	at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
	at com.mysql.cj.util.Util.getInstance(Util.java:167)
	at com.mysql.cj.util.Util.getInstance(Util.java:174)
	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries(StatementImpl.java:1033)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:839)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795)
	at compatibility.old.TestInterestingCases.mysqlBatchTest(TestInterestingCases.java:283)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
	at java.base/java.lang.reflect.Method.invoke(Method.java:578)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';INSERT INTO t0 VALUES (-1)' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:780)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries(StatementImpl.java:1019)
	... 31 more

How to repeat:
@Test
public void mysqlBatchTest() {
        try {
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3366?user=root&password=123456&allowMultiQueries=true");
            Statement statement = con.createStatement();
            statement.execute("DROP DATABASE IF EXISTS test;");
            statement.execute("CREATE DATABASE test;");
            statement.execute("USE test;");
            statement.execute("CREATE TABLE t0(c0 SMALLINT NOT NULL);");
            statement.addBatch("INSERT INTO t0 VALUES (-1);");
            statement.addBatch("INSERT INTO t0 VALUES (-1);");

            int[] res = statement.executeBatch();
            for (int r : res) {
                System.out.println(r);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

Suggested fix:
This looks like it's due to the incorrect addition of a ;.
[10 Mar 2023 7:20] MySQL Verification Team
Hello mingshi wu,

Thank you for the report and test case.
Quoting from manual "allowMultiQueries - Allow the use of ";" to delimit multiple queries during one statement. This option does not affect the 'addBatch()' and 'executeBatch()' methods, which rely on 'rewriteBatchStatements' instead. ".   Could you please try with rewriteBatchStatements=true?

-
import java.sql.*;

public class Bug110323 {

    public static void main(String[] args) throws Exception {

        try ( Connection con = getMySqlConnection()) {

            Statement statement = con.createStatement();
            statement.execute("DROP DATABASE IF EXISTS bug110323;");
            statement.execute("CREATE DATABASE bug110323;");
            statement.execute("USE bug110323;");
            statement.execute("CREATE TABLE t0(c0 SMALLINT NOT NULL);");
            statement.addBatch("INSERT INTO t0 VALUES (-1);");
            statement.addBatch("INSERT INTO t0 VALUES (-1);");
            int[] res = statement.executeBatch();
            for (int r : res) {
                System.out.println(r);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getMySqlConnection() throws Exception {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost/test?user=root&password=mysql123&rewriteBatchStatements=true";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url);
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
        System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());
        return conn;
    }  
    
}
-
ant -f C:\\Work\\Connectors\\MySQLJava\\Bug110323 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\Connectors\MySQLJava\Bug110323\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\Connectors\MySQLJava\Bug110323\build\built-jar.properties
Compiling 1 source file to C:\Work\Connectors\MySQLJava\Bug110323\build\classes
compile:
run:
MySQL Server: 8.0.32
Driver Name & Ver: MySQL Connector/Jmysql-connector-j-8.0.32 (Revision: fa4912a849140828e48162a2c396c8df0091bed7)
1
1
BUILD SUCCESSFUL (total time: 2 seconds)

- But if you use allowMultiQueries=true instead of rewriteBatchStatements=true then I can see as you reported i.e. syntax error:

ant -f C:\\Work\\Connectors\\MySQLJava\\Bug110323 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\Connectors\MySQLJava\Bug110323\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\Connectors\MySQLJava\Bug110323\build\built-jar.properties
Compiling 1 source file to C:\Work\Connectors\MySQLJava\Bug110323\build\classes
compile:
run:
MySQL Server: 8.0.32
Driver Name & Ver: MySQL Connector/Jmysql-connector-j-8.0.32 (Revision: fa4912a849140828e48162a2c396c8df0091bed7)
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 ';INSERT INTO t0 VALUES (-1)' at line 1
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
	at com.mysql.cj.util.Util.getInstance(Util.java:167)
	at com.mysql.cj.util.Util.getInstance(Util.java:174)
	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries(StatementImpl.java:1033)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:839)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795)
	at Bug110323.main(Bug110323.java:17)
Caused by: java.sql.SQLSyntaxErrorException: 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 ';INSERT INTO t0 VALUES (-1)' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:780)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries(StatementImpl.java:1019)
	... 3 more
BUILD SUCCESSFUL (total time: 2 seconds)

This sounds like an expected behavior to me. 

regards,
Umesh
[10 Mar 2023 7:54] Wenqian Deng
Thanks for your quick response!
If I only set rewriteBatchStatements=true, the test cast can run properly. However, when I set both rewriteBatchStatements=true and allowMultiQueries=true, I got the same syntax error "check the manual that corresponds to your MySQL server version for the right syntax to use near ';INSERT INTO t0 VALUES (-1)'"
I am confused by two things:
1. Why does allowMultiQueries=true affect executeBatch() while the manual says "allowMultiQueries does not affect the
'addBatch()' and 'executeBatch()' methods, which rely on
'rewriteBatchStatements' instead. " 
2. Why a syntax error is thrown when executing a syntactically correct statement. I am not expected setting allowMultiQueries=true will cause me such trouble.
[10 Mar 2023 8:35] MySQL Verification Team
Agree, with both rewriteBatchStatements=true & allowMultiQueries=true in use then we can see the issue -

import java.sql.*;

public class Bug110323 {

    public static void main(String[] args) throws Exception {

        try ( Connection con = getMySqlConnection()) {

            Statement statement = con.createStatement();
            statement.execute("DROP DATABASE IF EXISTS bug110323;");
            statement.execute("CREATE DATABASE bug110323;");
            statement.execute("USE bug110323;");
            statement.execute("CREATE TABLE t0(c0 SMALLINT NOT NULL);");
            // statement.executeUpdate("INSERT INTO t0 VALUES (-1);INSERT INTO t0 VALUES (-1);");            
            statement.addBatch("INSERT INTO t0 VALUES (-1);");
            statement.addBatch("INSERT INTO t0 VALUES (-1);");
            int[] res = statement.executeBatch();
            for (int r : res) {
                System.out.println(r);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getMySqlConnection() throws Exception {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost/test?user=root&password=mysql123&rewriteBatchStatements=true&allowMultiQueries=true";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url);
        DatabaseMetaData meta = conn.getMetaData();
        System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
        System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());
        return conn;
    }  
    
}

ant -f C:\\Work\\Connectors\\MySQLJava\\Bug110323 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\Connectors\MySQLJava\Bug110323\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\Connectors\MySQLJava\Bug110323\build\built-jar.properties
Compiling 1 source file to C:\Work\Connectors\MySQLJava\Bug110323\build\classes
compile:
run:
MySQL Server: 8.0.32
Driver Name & Ver: MySQL Connector/Jmysql-connector-j-8.0.32 (Revision: fa4912a849140828e48162a2c396c8df0091bed7)
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 ';INSERT INTO t0 VALUES (-1)' at line 1
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
	at com.mysql.cj.util.Util.getInstance(Util.java:167)
	at com.mysql.cj.util.Util.getInstance(Util.java:174)
	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries(StatementImpl.java:1033)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:839)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795)
	at Bug110323.main(Bug110323.java:17)
Caused by: java.sql.SQLSyntaxErrorException: 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 ';INSERT INTO t0 VALUES (-1)' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:780)
	at com.mysql.cj.jdbc.StatementImpl.executeBatchUsingMultiQueries(StatementImpl.java:1019)
	... 3 more
BUILD SUCCESSFUL (total time: 2 seconds)