| 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."
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.