Description:
Hi,
I found that if the table(or column) name contains "SELECT", rewriteBatchedStatements=true won't work, which means the driver will send multiple INSERT queries one by one instead of rewriting the queries and sending only one INSERT.
How to repeat:
Code to repeat:
// CREATE TABLE `NORMAL_TABLE` (
// `COL` varchar(10) NOT NULL
// )
// CREATE TABLE `TABLE_WITH_SELECT` (
// `COL` varchar(10) NOT NULL
// )
Class.forName("com.mysql.jdbc.Driver").newInstance();
String jdbcString = "jdbc:mysql://yourURL/yourDB";
Properties props = new Properties();
props.setProperty("user", "username");
props.setProperty("password", "password");
props.setProperty("rewriteBatchedStatements", "true");
Connection conn = DriverManager.getConnection(jdbcString, props);
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement("INSERT INTO NORMAL_TABLE (COL) VALUES (?)");
for (int i = 0; i < 10; i++) {
stmt.setString(1, "" + i);
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
stmt = conn.prepareStatement("INSERT INTO TABLE_WITH_SELECT (COL) VALUES (?)");
for (int i = 0; i < 10; i++) {
stmt.setString(1, "" + i);
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
conn.close();
Logs in general_log:
143624 Query /* mysql-connector-java-5.1.44 ( Revision: b3cda4f864902ffdde495b9df93937c3e20009be ) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS charact
er_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect A
S 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, @@n
et_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 A
S tx_isolation, @@wait_timeout AS wait_timeout
143624 Query SET NAMES latin1
143624 Query SET character_set_results = NULL
143624 Query SET autocommit=1
143624 Query SET sql_mode='STRICT_TRANS_TABLES'
143624 Query SET autocommit=0
143624 Query select @@session.tx_read_only
143624 Query INSERT INTO NORMAL_TABLE (COL) VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
143624 Query commit
143624 Query select @@session.tx_read_only
143624 Query select @@session.tx_read_only
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('0');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('1');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('2');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('3');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('4');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('5');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('6');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('7');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('8');
143624 Query INSERT INTO TABLE_WITH_SELECT (COL) VALUES ('9')
143624 Query commit
Suggested fix:
I feel this should be a bug in parser for the query. A workaround I found is using back quote to wrap the table and column name like this:
stmt = conn.prepareStatement("INSERT INTO `TABLE_WITH_SELECT` (COL) VALUES (?)");