Geek Answers Handbook

Mysql auto-increment is not consistent in innodb

Mysql auto-increment is not sequential.

when I tried to load bulk csv data, it was missing part of the auto increment id

Auto Increment ID is not consistent

Db engine is innodb and id is auto increment

Example

  id color 
     1 red
     2 blue
     3 green
     4 orange
     9 pink
     10 white
     11 black 
     16 gray
     17 brown

The identifier is not sequential.

+4
source share
2 answers

they don’t know about codeignitor and transaction specifics, but I know that "caught failures" in requests (for example, insert ignore ) have the effect of NOT inserting, but DO increase auto_increment. Find your answer here. And for the rest, do not bother to “fix” it.

+3
source

If you are using MySQL newer than 5.1.22 , this may be due to the InnoDB blocking strategy , as Miguel ngel Nieto explains in this blog post .

You can change this setting of innodb_autoinc_lock_mode parameter:

  • innodb_autoinc_lock_mode = 0 : "traditional" locking mode , the same behavior as before 5.1.22
  • innodb_autoinc_lock_mode = 1 : "sequential" locking mode , default behavior, this is probably what you set, it is intended to improve concurrency and performance with a lack of holes in the sequence.
  • innodb_autoinc_lock_mode = 2 : interleaved locking mode , this is the fastest and most scalable locking mode, but it is not safe when used with replication instructions or recovery scripts when SQL queries are played from a binary log

For more information on using innodb_autoinc_lock_mode you can check the detailed mysql documentation .

+1
source

More articles:


All Articles