Gorm Batch insert fails to return correct auto-incremented primary key when auto_increment is set to 2 in mysql

GORM Playground Link

https://github.com/go-gorm/playground/pull/254/files

Description

My mysql is configured with auto_increment_increment=2 for HA reasons.

When I attempt to insert multiple rows by passing an array of row objects to db.Create(...), I expect it to fill in the ID (primary-key) correctly in return. Though my db is updated correctly, the Create() is found to populate sequential ID value. This is wrong for all the newly added rows except the first one.

Am I missing anything in my code or is there a workaround I can use?

Comment From: jinzhu

https://gorm.io/docs/models.html#Fields-Tags

Change tag autoIncrementIncrement to 2

Comment From: balajikrishnanextreme

Can this be made a db open time global config, just like config in mysql? With 50+ tables, it is hard to update all the models

Comment From: jay-wlj

Can this be made a db open time global config, just like config in mysql? With 50+ tables, it is hard to update all the models

I have the same problem

Comment From: willthrom

Hi, This is quite critical for DB with HA, like using MYSQL NDB Cluster. The ID's should come from the DB not from an internal calculation.

At least, if it is going to be calculated internally, to check against mysql the value for autoIncrementIncrement for that table and use it accordantly without the need to specified (or only for creation of the table, but if the table already exist, to read it from Mysql)

https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increment_increment

Comment From: balajikrishnanextreme

My deployment has both single node mode and in HA mode option, so I could not fix a number for this tag at compile time.

As a workaround, I have explicitly set a value of 2 in my model definition as well as in mysql configuration irrespective of my HA deployment option. This is a one time work, but it has been working fine so far.

Comment From: willthrom

This should be easy to fix just querying MySQL for the current increment configure for the table and global, and adapt accordingly. This should be done when auto migration is not used (99% of the cases in production environments).

Not sure if the 'returning' statement will work here as work around. But the current gorm manually assignment of the ID for batch insert processing is kind of broken.

At least It should be added to the documentation that problem.