Bug #77183 INSERT..VALUE..lead to invalidation of batch insert
Submitted: 28 May 2015 14:39 Modified: 18 Apr 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

[28 May 2015 14:39] sun sun
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.
[1 Jun 2015 22:34] Filipe Silva
Hi Sun Sun,

Very nice catch! Thank you for this bug report.
[29 Dec 2016 2:39] sun sun
Is this fixed´╝č
[18 Apr 0:32] Daniel So
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."