Gorm MySQL / Error 1869: Auto-increment value in UPDATE conflicts with internally generated values

GORM Playground Link

https://github.com/go-gorm/playground/pull/212

Description

Hello,

When creating a record with Create(), even if using Clauses(clause.OnConflict{DoNothing: true }), Gorm is appending "ON DUPLICATE KEY UPDATE id=id" which causes an error from MySQL if there's a custom AUTO_INCREMENT primary KEY (and some other unique fields) :

Error 1869: Auto-increment value in UPDATE conflicts with internally generated values

I think it would be better to do "INSERT IGNORE" when using "Clauses(clause.OnConflict{DoNothing: true })".

There is also a problem of performance. There is some overhead compared to "INSERT IGNORE". Especially when using triggers :

« A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger activates for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row. » Source: https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html

I found a workaround using the following Clause instead of DoNothing: true :

Clauses(clause.Insert{Modifier: "IGNORE"}).

Comment From: jinzhu

ON DUPLICATE KEY UPDATE id=id won't cause any update, there are some optimizations in mysql, if so, I would like to keep it like right now.

To fix this, you can override the ON CONFLICT, INSERT builder for mysql

Comment From: edwintcloud

@jinzhu can you please reference how to "override the ON CONFLICT, INSERT builder for mysql" to fix this issue..?

Comment From: Jcodelove

@jinzhu can you please reference how to "override the ON CONFLICT, INSERT builder for mysql" to fix this issue..?

db.Table("XX").Clauses(clause.Insert{Modifier: "IGNORE"}).Create(xx)