MySQL Bugs: #77183: INSERT..VALUE..lead to invalidation of batch insert

Bug #77183	INSERT..VALUE..lead to invalidation of batch insert
Submitted:	28 May 2015 14:39	Modified:	18 Apr 2020 0:32
Reporter:	sun sun	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.35	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	batch, insert

Description:
In mysql,you can insert a row like:
insert into t1 (c1,c2) values (1,1)
or
insert into t1 (c1,c2) value (1,1).

This two sqls are equivalent in mysql syntax.

In mysql-connector-java, we insert a lot of data with batch insert like:

ps.addBatch();
ps.executeBatch();

and rewriteBatchedStatements=true.

But when you use 'values' in sql, it works ok, but when you use 'value' in sql, it inserts one by one without batch.

How to repeat:
PreparedStatement preStmt = conn.prepareStatement("insert into griddata (model,valuename,level,time,aging) value (?,?,?,?,?)");
for (int aging = 1; aging <= 80; aging++) {
      preStmt.setString(1, "aa");
      preStmt.setString(2, "aa");
      preStmt.setInt(3, 1);
      preStmt.setString(4, "aa");
      preStmt.setInt(5, 2);
      preStmt.addBatch();
}
preStmt.executeBatch();

You can repeat it easyily with a batch insert as "insert into t1 (c1,c2) value (?,?)"

Suggested fix:
In com.mysql.jdbc.PreparedStatement.executeBatchedInserts(int batchTimeout),check both valuesClause and valueClause.

Hi Sun Sun,

Very nice catch! Thank you for this bug report.

Is this fixed？

Posted by developer:
 
Added the following entry to the Connector/J 5.1.49 changelog:

"Inserting values in batch using a PreparedStatement failed for an INSERT ...VALUE statement but worked for an INSERT ... VALUES statement, while they are synonymous for MySQL."