mysql autoincrement is not sequential in innodb

Question

Mysql autoincrmenting is not sequential.

when i was trying to upload csv bulk data it's skipping some of the auto increment id

The auto increment id is not sequential

The db engine is innodb & 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 id is not sequential.

Answer 1

don't know about codeignitor and specifics of transactions, but i do know that "catched fails" in queries (like insert ignore) have the effect of NOT inserting, but DO increment the auto_increment. Look for your answer here. And for the rest don't bother 'fixing' this.

Answer 2

If you are using MySQL newer than 5.1.22 this can be caused because of the InnoDB locking strategy as Miguel Angel Nieto explains in this blog post.

You can change this behavior setting the innodb_autoinc_lock_mode parameter:

• innodb_autoinc_lock_mode = 0: “traditional” lock mode, same behavior as before 5.1.22
• innodb_autoinc_lock_mode = 1: “consecutive” lock mode, default behavior, this is probably what you have set, it's designed to improve concurrency and performance with the drawback of having holes on the sequence.
• innodb_autoinc_lock_mode = 2: “interleaved” lock mode, this is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log

For a detailed information o the use of innodb_autoinc_lock_mode you can check the detailed mysql documentation.