| Bug #99604 | Add support to row alias on INSERT... ON DUPLICATE KEY UPDATE on batch mode | ||
|---|---|---|---|
| Submitted: | 16 May 2020 19:04 | Modified: | 17 Nov 2022 18:06 |
| Reporter: | Alisson Oliveira | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 8.0.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jul 2020 11:13]
MySQL Verification Team
Hello Alisson Oliveira, Thank you for the report and test case. Verified as described. regards, Umesh
[13 Jul 2020 11:15]
MySQL Verification Team
-table structure
create table costumes
(
owner_id int unsigned not null,
id smallint not null,
amount smallint default 0 not null,
ready tinyint(1) default 0 not null, primary key(id)
);
-
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
public class Bug99604 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=mysql123!&serverTimezone=UTC&rewriteBatchedStatements=true");
Runtime.Version version = Runtime.version();
System.out.println("JDK version: " + version);
DatabaseMetaData meta = connection.getMetaData();
System.out.println("MySQL Server: " + meta.getDatabaseProductVersion());
System.out.println("Driver Name & Ver: " + meta.getDriverName() + meta.getDriverVersion());
try{
var collection = List.of(new int[]{ 32970,2,5,0 }, new int[]{ 32970,3,2,0 }, new int[]{ 32970,4,3,0 });
var sql = "INSERT INTO costumes (owner_id, id, amount, ready) VALUES (?, ?, ?, ?) AS T ON DUPLICATE KEY UPDATE costumes.amount = costumes.amount + T.amount";
var statement = connection.prepareStatement(sql);
for (var obj : collection) {
statement.setInt(1, obj[0]);
statement.setInt(2, obj[1]);
statement.setInt(3, obj[2]);
statement.setInt(4, obj[3]);
statement.addBatch();
}
System.out.println(statement);
statement.executeBatch();
}catch(SQLException e){
e.printStackTrace();
}
}
}
--
ant -f C:\\Work\\MySQLJava\\Bug99604 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\Bug99604\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\Bug99604\build\built-jar.properties
Compiling 1 source file to C:\Work\MySQLJava\Bug99604\build\classes
compile:
run:
JDK version: 11.0.3+12-LTS
MySQL Server: 8.0.20
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b)
com.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO costumes (owner_id, id, amount, ready) VALUES (32970, 4, 3, 0) AS T ON DUPLICATE KEY UPDATE costumes.amount = costumes.amount + T.amount
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',(32970, 3, 2, 0) AS T ,(32970, 4, 3, 0) AS T ON DUPLICATE KEY UPDATE costumes.' at line 1
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
at com.mysql.cj.util.Util.getInstance(Util.java:167)
at com.mysql.cj.util.Util.getInstance(Util.java:174)
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:755)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
at Bug99604.main(Bug99604.java:33)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',(32970, 3, 2, 0) AS T ,(32970, 4, 3, 0) AS T ON DUPLICATE KEY UPDATE costumes.' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:716)
... 3 more
BUILD SUCCESSFUL (total time: 2 seconds)
[19 Jul 2020 5:54]
MySQL Verification Team
Bug #100252 marked as duplicate of this one
[17 Nov 2022 18:06]
Filipe Silva
Fixed in MySQL Connector/J 8.0.29 after ParseInfo/QueryInfo under fix for Bug#106240.
[21 Dec 2022 18:05]
Daniel So
Posted by developer: Added the following entry to the Connector/J 8.0.32 changelog: "Insert statements with ON DUPLICATE KEY UPDATE that used value aliases caused a syntax error when the batched statements were being rewritten. It was because the value alias were being added repeatedly after each value group while only one alias was expected. This fix eliminates the undue repetitions."
Description: The new row alias is being considered part of valuesClause on ParseInfo class. When executing an Insert with ON DUPLICATE KEY UPDATE in batch mode, the row alias is repeated alongside the values. The execution fails with: Caused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',(32970,2,5,0) AS new ,(32970,3,2,0) AS new ,(32970,4,3,0) AS new ,(32970,5,1,0)' at line 1 How to repeat: 1. mysql-connector-java: 8.0.20 2. Table structure: create table costumes ( owner_id int unsigned not null, id smallint not null, amount smallint default 0 not null, ready tinyint(1) default 0 not null, ); 2. Execution Code: var collection = List.of(new int[]{ 32970,2,5,0 }, new int[]{ 32970,3,2,0 }, new int[]{ 32970,4,3,0 }); var sql = "INSERT INTO costumes (owner_id, id, amount, ready) VALUES (?, ?, ?, ?) AS new ON DUPLICATE KEY UPDATE amount = amount + new.amount"; var statement = con.prepareStatement(sql); for (var obj : collection) { statment.setInt(1, obj[0]); statment.setInt(2, obj[1]); statment.setInt(3, obj[2]); statment.setInt(4, obj[3]); statement.addBatch(); } statement.executeBatch();