2

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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.