MySQL auto increment column increases after insertion error occurs

Question

I was doing some insertion tests in my database and I realized that when the INSERT statement fails, due error 1452, causes the auto_increment value increase by 1. Even though, there wasn't any row inserted in this statement.

Thereby, this behavior causes the next valid insertion skip an available and possible value for the auto_increment.

Example:

1st Insert | Result: Success | id(auto_increment_column): 1 |
2nd Insert | Result: Failure | -------- No rows were recorded---- |
3nd Insert | Result: Success | id(auto_increment_column): 3 |

I want to know if someone had this problem before and if this is really a problem.

Cheers.

P.S.:

Errno: 1452 - fk constraint fails. This means that I'm trying to reference some column value in another table and this value doesn't exists there.

• Database: MySQL Ver 14.14 Distrib 5.6.13, for Windows 64-bit
• Storage Engine: InnoDB

• Hannah Vernon: This is expected behavior for an auto-increment column. If you require there to be no gaps you would need to roll your own number-generation method.
• Colin 't Hart: And even then you'd have a gap if you deleted a row.
• ypercubeᵀᴹ: Several other reasons for gaps to appear in an auto incremented column: If MAX(id) != COUNT(id), does that mean rows have been deleted?

Answer 1

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says

You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

Therefore, if the second INSERT had failed and rolled back, it makes the gap.

You may have to set innodb_autoinc_lock_mode to 0 or 2. Please read the MySQL Documentation on Configurable InnoDB Auto-Increment Locking to understand what changing innodb_autoinc_lock_mode can do for you.

Answer 2

Where your table design is

id int auto_increment primary key,
field1 int,
field2 int,
unique u_f1_f2 ( field1, field2 )

You can try insert like :

insert ignore into table select (select max(id)+1 from table), "value1", "value2" ;

This will try

• insert new data with last unused id (not autoincrement)
• if in unique fields duplicate entry found ignore it
• else insert new data normally
  
  ( but this method not support to update fields if duplicate entry found )