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