| Bug #84813 | rewriteBatchedStatements fails in INSERT | ||
|---|---|---|---|
| Submitted: | 3 Feb 2017 17:34 | Modified: | 1 Aug 2018 22:28 |
| Reporter: | Yiftach Kaplan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[6 Feb 2017 5:50]
Chiranjeevi Battula
Hello Yiftach Kaplan, Thank you for the bug report and test case. Verified this behavior on MySQL Connector / J 5.1.40. Thanks, Chiranjeevi.
[6 Feb 2017 5:51]
Chiranjeevi Battula
run: Mon Feb 06 10:49:31 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. 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 '( 1001,1001)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1162) at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1582) at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1248) at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:958) at javaapplication3.bug_84813.main(bug_84813.java:31) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '( 1001,1001)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098) at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1543) ... 3 more BUILD SUCCESSFUL (total time: 1 second)
[9 Mar 2017 7:43]
cai feiji
My program also encountered this problem while you put a ';' in the last of insert sql
[1 Aug 2018 22:28]
Daniel So
Posted by developer: Added the following entry to the Connector/J 5.1.47 changelog: "Rewriting prepared INSERT statements in a multiquery batch failed with a BatchUpdateException when the statements did not contain place holders. This was due a faulty mechanism for query rewritting, which has been corrected by this fix."
[5 Oct 2018 21:21]
Daniel So
Posted by developer: Also added the changelog entry for Connector/J 8.0.13.
Description: When I'm trying to run INSERT INTO VALUES using JDBC with rewriteBatchedStatements I'm getting an exception. How to repeat: In Java: public static void main(String[] args) throws Exception { try (Connection connection = DriverManager.getConnection("jdbc:mysql://yvb0:3306/aaa?rewriteBatchedStatements=true", "user", "password"); Statement statement = connection.createStatement(); PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO aaa VALUES (0, RAND()*1000, RAND()*10000);");) { statement.executeUpdate("DROP TABLE IF EXISTS aaa;"); statement.executeUpdate("CREATE TABLE aaa (a0 BIGINT AUTO_INCREMENT PRIMARY KEY, a1 BIGINT, a2 BIGINT);"); preparedStatement.addBatch(); preparedStatement.addBatch(); preparedStatement.executeBatch(); } } Exception: Exception in thread "main" java.sql.BatchUpdateException: No value specified for parameter 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160) at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1582) at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1248) at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959) at org.stam.SimpleTest.main(SimpleTest.java:19) Caused by: java.sql.SQLException: No value specified for parameter 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862) at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2205) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2185) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2052) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1543) ... 3 more When I'm trying to add values: try (Connection connection = DriverManager.getConnection("jdbc:mysql://yvb0:3306/aaa?rewriteBatchedStatements=true", "user", "password"); Statement statement = connection.createStatement(); PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO aaa VALUES (0, ?, RAND()*10000);");) { statement.executeUpdate("DROP TABLE IF EXISTS aaa;"); statement.executeUpdate("CREATE TABLE aaa (a0 BIGINT AUTO_INCREMENT PRIMARY KEY, a1 BIGINT, a2 BIGINT);"); preparedStatement.setInt(1, 1); preparedStatement.addBatch(); preparedStatement.setInt(1, 2); preparedStatement.addBatch(); preparedStatement.executeBatch(); } I'm getting: Exception in thread "main" 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 '(0, 2, RAND()*10000)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160) at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1582) at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1248) at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959) at org.stam.SimpleTest.main(SimpleTest.java:21) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '(0, 2, RAND()*10000)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1543) ... 3 more