Hi. The issue with ID's jumping is on
MySQL with default
InnoDB tables. When upserting a lot of data with duplicate entries, there is a possibility to reach the maximum unsigned INT value (4_294_967_295).
I found two solutions, but neither the perfect one:
- Changing the auto incremented ID column to unsigned BIGINT (18_446_744_073_709_551_615).
- Changing table storage engine from InnoDB to MyISAM, which have not this auto incrementing issue.
The reason why InnoDB have this auto incrementing issue is probably because of transactions support with the rollback function, which MyISAM does not have. Another main difference between InnoDB and MyISAM is, that MyISAM have full table lock instead of InnoDB's row lock while inserting/updating, which could cause slowing while upserting a lot of data during query run.
It looks like the MariaDB have this issue too, while it is a design decision of InnoDB as I mentioned above.
This MySQL's InnoDB behaviour should be mentioned in CodeIgniter's upsert documentation I think.
