Presto: mysql connector with GTID mode error " Duplicate entry '1' for key 'PRIMARY' "

[getGitxxx]

hi, @ebyhr I found you have fixed issue #2251 in version 327. well done!
when testing it again, I get another error:

 io.prestosql.spi.PrestoException: Duplicate entry '1' for key 'PRIMARY'
    at io.prestosql.plugin.jdbc.BaseJdbcClient.finishInsertTable(BaseJdbcClient.java:570)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$finishInsertTable$19(StatisticsAwareJdbcClient.java:192)
    at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:47)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.finishInsertTable(StatisticsAwareJdbcClient.java:192)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.finishInsertTable(ForwardingJdbcClient.java:144)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.finishInsertTable(ForwardingJdbcClient.java:144)
    at io.prestosql.plugin.jdbc.JdbcMetadata.finishInsert(JdbcMetadata.java:258)
    at io.prestosql.metadata.MetadataManager.finishInsert(MetadataManager.java:790)
    at io.prestosql.sql.planner.LocalExecutionPlanner.lambda$createTableFinisher$3(LocalExecutionPlanner.java:2850)
    at io.prestosql.operator.TableFinishOperator.getOutput(TableFinishOperator.java:297)
    at io.prestosql.operator.Driver.processInternal(Driver.java:379)
    at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
    at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
    at io.prestosql.operator.Driver.processFor(Driver.java:276)
    at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1075)
    at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
    at io.prestosql.$gen.Presto_327____20191221_104034_2.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' 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:423)
    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:3933)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2439)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:829)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:729)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.execute(BaseJdbcClient.java:810)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.finishInsertTable(BaseJdbcClient.java:567)
    ... 20 more

It looks like when executing " insert into target_table select ...", the temp_table's id(auto_increment) always begins at ‘1’ but expect to generate max(target_table_id)+1 which can append insert new rows to target table successfully.

[ebyhr]

Sorry for bothering you. Let me check the details.

[ebyhr]

@getGitxxx Could you share the steps to reproduce (DDL & DML)? I can reproduce it easily, but I would like to make sure the situation just in case.

[getGitxxx]

@ebyhr Thanks reply quickly.

step1: create table SQL

CREATE TABLE `user_tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

step2: execute insert query first time, it works fine.

INSERT INTO test.user_tmp(name)
SELECT 
cast(name as varchar(32))
FROM test.user_temp4 LIMIT 10;
---------
INSERT: 10 row

Query 20191223_070031_00003_br3ma, FINISHED, 1 node
Splits: 35 total, 35 done (100.00%)
0:01 [1 rows, 0B] [1 rows/s, 0B/s]

step3: repeat step2 ,will goes fail.
Query 20191223_070221_00005_br3ma failed: Duplicate entry '1' for key 'PRIMARY'

[ebyhr]

@getGitxxx Thanks! I think we should omit the primary key from INSERT statement to the final table (≠temp table) in this case.

[findepi]

This is likely covered by https://github.com/prestosql/presto/issues/1185

[getGitxxx]

@getGitxxx Thanks! I think we should omit the primary key from INSERT statement to the final table (≠temp table) in this case.

hi, @ebyhr any plan to fix this bug？

[ebyhr]

@getGitxxx Sorry for my late response. I confirmed this issue was fixed in version 329. Could you try the latest version?
https://prestosql.io/download.html

[getGitxxx]

@ebyhr I have tried the latest version 329，The issue still exists.

[Praveen2112]

@getGitxxx Can you check with the master branch. It is in under the milestore 330. 330 will be release in a few days I guess.

[getGitxxx]

@Praveen2112 will try it.

[Praveen2112]

@getGitxxx I have tested with the master branch and the above issue is fixed by #2265. Feel free to re-open this issue if the above patch didn't work

[getGitxxx]

Hi @Praveen2112 ,

Sorry for confirming the patch late, It's worked for me. Thanks a lot!