INSERT ... ON DUPLICATE KEY UPDATE ,Reporting the following error

[xfyang1989]

sql:
insert into t_order (id, avl_inventory, type,
create_person, create_time, update_person, update_time, is_deleted, version )
VALUES
(76667, 100, 1, 'xx(189870)', '2019-4-25 18:25:20', 'xx(189870)','2019-4-25 18:25:20', 0,0 ) ,
(78143, 100, 1, 'xx(189870)', '2019-4-25 18:25:20',
'xx(189870)', '2019-4-25 18:25:20', 0,0 )
ON DUPLICATE KEY UPDATE avl_inventory = VALUES(avl_inventory),
type = VALUES(type),
create_time = VALUES(create_time), create_person = VALUES(create_person),
update_time = VALUES(update_time), update_person = VALUES(update_person);

error:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '78143' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1283)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:783)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:65)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:61)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:69)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)

[xfyang1989]

This function is OK in 3.0.1. Is it not possible to go to 4.0.0-RC1?

[xfyang1989]

Mysql "ON DUPLICATE KEY UPDATE" statement is not inserted, there are updates, should not report primary key conflict?

[xfyang1989]

I feel that this 4.0.0-RC1 introduces a new bug, and these features are OK in version 3.0.1. I have used the smallest version of 4.0.0 in the project to keep the wrong version. . I feel that I will not continue to upgrade in the future.

[xfyang1989]

Actual SQL: rw0 ::: insert into t_order
(id, avl_inventory, type, create_person, create_time, update_person, update_time, is_deleted, version)
VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0) ::: [xx, xx, 1, admin, 2019-03-22 17:39:31.0, admin, 2019-03-22 17:39:31.0]

4.0.0-RC1 seems to cut off the statement behind me, do you see if it is?

[xfyang1989]

org.apache.shardingsphere.core.BaseShardingEngine#rewriteAndConvert(final String sql, final List parameters, final SQLRouteResult sqlRouteResult)
org.apache.shardingsphere.core.rewrite.SQLBuilder#toSQL(final TableUnit tableUnit, final Map<String, String> logicAndActualTableMap, final ShardingRule shardingRule, final ShardingDataSourceMetaData shardingDataSourceMetaData)

[codefairy08]

@xfyang1989 Thanks for your analysis, I will investigate the cause of the error.

[xfyang1989]

@codefairy08 I want to use the mysql syntax（INSERT ... ON DUPLICATE KEY UPDATE）, is there any other way?

[xfyang1989]

Consult a question, if I configure the default-data-source, other tables that do not need to be sub-tables can be queried by the original sql?

[codefairy08]

all tables are not sharding table, it will be exexuted with the original sql.
please detail in org.apache.shardingsphere.core.route.type.standard.SubqueryRouteTest

[xfyang1989]

Is there any other way to avoid this problem? Minimal change

[zlanzip]

    private void appendInsertValuesToken(final SQLBuilder sqlBuilder, final InsertValuesToken insertValuesToken, final int count, final InsertOptimizeResult insertOptimizeResult) {
        for (InsertOptimizeResultUnit each : insertOptimizeResult.getUnits()) {
            encryptInsertOptimizeResultUnit(insertOptimizeResult.getColumnNames(), each);
        }
        sqlBuilder.appendPlaceholder(new InsertValuesPlaceholder(sqlStatement.getTables().getSingleTableName(), insertOptimizeResult.getColumnNames(), insertOptimizeResult.getUnits()));
        appendRest(sqlBuilder, count, originalSQL.length());
    }

    private void appendRest(final SQLBuilder sqlBuilder, final int count, final int startIndex) {
        int stopPosition = sqlTokens.size() - 1 == count ? originalSQL.length() : sqlTokens.get(count + 1).getStartIndex();
        sqlBuilder.appendLiterals(originalSQL.substring(startIndex, stopPosition));
    }

Oh,the length of the originalSQL is the startIndex !!!

[xfyang1989]

When will the official version of 4.0.0-RC2 be released?

[tristaZero]

@xfyang1989 @zlanzip
Hi, thanks for your attention, we fixed this bug on #2348, and more improvements of INSERT VALUES()or INSERT SET will be done recently.