Mysql Autoincrement Value Increases Even When Insertion Fails Due To Error

As I receive data from the user, I validate the format of the input every time (PHP). The only thing I can't validate (or I wouldn't want to) is the constraint (for example foreign

Solution 1:

That is how auto increment values and sequences work. When a value is used, it is not reclaimed if the transaction fails, and the transaction is rolled back.

Since you are using auto increment values, the actual value does not matter so the spaces should not be a problem, apart from the aesthetics of having missing numbers.

Solution 2:

We should do check if there an error before insert it, by using Stored Procedure and create an Raise Application Error for every error condition, so that our auto_increment will never increase when there is an error in insert proses. It because when an error occur inside the stored procedure you can set condition when the insert proces can be execute. When error accout set a signal state = 1 or something and when signal state == 1 end end the Stored Procedure and do not execute insert stmt. It something like that.

And the second option is to reset the auto_increment, when error occur.

ALTERTABLE table_name AUTO_INCREMENT = ?

But we need to know the last_id for set the auto_increment, event you doesn't need to know the last_id value has auto_increment by setting equal to 1, MySQL will set the next auto_increment equal to max key of record + 1.

Lets say in your database max id is 7, then you trying to insert two new record but those both return an error then make the auto_increment will increase and turn into 10. Then you do ALTER TABLE table_name AUTO_INCREMENT = 1; The auto_increment will back into 8.

Those both are the way to control auto_increment value when error acour and of course before insert into database on application layer we should handle it in validation step so when insert into database there should be not any error, if we carefully handle this in application layer.

Solution 3:

You may have to set innodb_autoinc_lock_mode to 0 or 2.