Bug #81063 w/ rewriteBatchedStatements, when 2 tables involved, the rewriting not correct
| Submitted: | 13 Apr 2016 9:10 | Modified: | 9 Aug 2018 14:45 |
|---|---|---|---|
| Reporter: | Su Dylan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.16, 5.1.38 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | jdbc 5.1.16 | ||
[13 Apr 2016 12:07]
Chiranjeevi Battula
Hello Su Dylan, Thank you for the bug report and test case. Verified this behavior on MySQL Connector / J 5.1.38. Thanks, Chiranjeevi.
[13 Apr 2016 12:08]
Chiranjeevi Battula
mysql-connector-java-commercial-5.1.38:
160413 16:12:37 135 Connect root@localhost on test
135 Query /* mysql-connector-java-commercial-5.1.38 ( Revision: fe541c166cec739c74cc727c5da96c1028b4834a ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
135 Query SET NAMES utf8
135 Query SET character_set_results = NULL
135 Query SET autocommit=1
135 Query select @@session.tx_read_only
135 Query drop table if exists t1,t2;
135 Query create table t1(c1 int, c2 int);
136 Connect U0319@localhost on mysql
136 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER'
136 Query SET SESSION wait_timeout=1800
136 Query SET character_set_results = NULL
136 Query SET autocommit=1
136 Query SELECT @@session.sql_mode
136 Quit
160413 16:12:38 135 Query create table t2(c1 int, c2 int)
135 Query select @@session.tx_read_only
135 Query insert into t1 values(10,10);
135 Query insert into t2 values(10,10),(11,11);
135 Query insert into t2 values(11,11)
135 Quit
137 Connect U0319@localhost on mysql
137 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER'
137 Query SET SESSION wait_timeout=1800
137 Query SET character_set_results = NULL
137 Query SET autocommit=1
137 Query SELECT @@session.sql_mode
137 Quit
mysql-connector-java-5.1.16:
160413 16:26:00 939 Connect root@localhost on test
939 Query /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
939 Query /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
939 Query SHOW COLLATION
939 Query SET NAMES utf8
939 Query SET character_set_results = NULL
939 Query SET autocommit=1
939 Query drop table if exists t1,t2;
940 Connect U0319@localhost on mysql
940 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER'
940 Query SET SESSION wait_timeout=1800
940 Query SET character_set_results = NULL
940 Query SET autocommit=1
940 Query SELECT @@session.sql_mode
160413 16:26:01 939 Query create table t1(c1 int, c2 int);
941 Connect U0319@localhost on mysql
941 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER'
941 Query SET SESSION wait_timeout=1800
941 Query SET character_set_results = NULL
941 Query SET autocommit=1
941 Query SELECT @@session.sql_mode
941 Quit
939 Query create table t2(c1 int, c2 int)
939 Query insert into t1 values(10,10);
160413 16:26:02 939 Query insert into t2 values(10,10),(11,11);
939 Query insert into t2 values(11,11)
939 Quit
942 Connect U0319@localhost on mysql
942 Query SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER'
942 Query SET SESSION wait_timeout=1800
942 Query SET character_set_results = NULL
942 Query SET autocommit=1
942 Query SELECT @@session.sql_mode
942 Quit
[9 Aug 2018 14:45]
Daniel So
Posted by developer: Added the following entry to the Connector/J 5.1.47 and 8.0.13 changelogs: "When using batched prepared statements with multiple queries per statement, queries rewriting was incorrect, resulting in the wrong queries being sent to the server."
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: general log: ============ 2016-04-13T09:02:55.948177Z 7 Connect root@xxx.xxx.xxx.xxx on test using TCP/IP 2016-04-13T09:02:55.983718Z 7 Query /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 2016-04-13T09:02:56.051467Z 7 Query SHOW WARNINGS 2016-04-13T09:02:56.083960Z 7 Query /* mysql-connector-java-5.1.16 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment 2016-04-13T09:02:56.113664Z 7 Query SHOW COLLATION 2016-04-13T09:02:56.152747Z 7 Query SELECT @@session.autocommit 2016-04-13T09:02:56.182223Z 7 Query SET NAMES utf8mb4 2016-04-13T09:02:56.211965Z 7 Query SET character_set_results = NULL 2016-04-13T09:02:56.240885Z 7 Query SET autocommit=1 2016-04-13T09:02:56.283820Z 7 Query drop table if exists t1,t2; 2016-04-13T09:02:56.288746Z 7 Query create table t1(c1 int, c2 int); 2016-04-13T09:02:56.294645Z 7 Query create table t2(c1 int, c2 int) 2016-04-13T09:02:56.335565Z 7 Query insert into t1 values(10,10); 2016-04-13T09:02:56.336257Z 7 Query insert into t2 values(10,10),(11,11); 2016-04-13T09:02:56.336924Z 7 Query insert into t2 values(11,11) 2016-04-13T09:02:56.366860Z 7 Quit Problem: ======= The original `insert` is inserting 2 rows to table `t1` and 2 rows to table `t2`. However, after the rewriting, it becomes 1 row in `t1` and 3 rows in `t2`. How to repeat: Jave code --------------------------- public class MultiStmts_mysql_bug_1 { public static void main(String[] args) throws SQLException { String url1 = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/test?characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true&user=root&password="; Connection conn = DriverManager.getConnection(url1); PreparedStatement stmt = null; try { // Create table stmt = conn.prepareStatement("drop table if exists t1,t2;" + "create table t1(c1 int, c2 int);" + "create table t2(c1 int, c2 int)"); stmt.execute(); // Insert data String sql = "insert into t1 values(?,?); insert into t2 values(?,?)"; stmt = conn.prepareStatement(sql); stmt.setLong(1, 10); stmt.setLong(2, 10); stmt.setLong(3, 10); stmt.setLong(4, 10); stmt.addBatch(); stmt.setLong(1, 11); stmt.setLong(2, 11); stmt.setLong(3, 11); stmt.setLong(4, 11); stmt.addBatch(); stmt.executeBatch(); } catch (Exception e) { e.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } Suggested fix: Even after rewriting, 2 rows is inserted into t1 and 2 rows into t2.