Will MySQL reuse deleted ID's when Auto Increment is applied

Question

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

That document I'm reading seems to say something like:

"In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group."

I don't really understand what's being said there. Aren't the values supposed to be reused automatically?

Thanks in advance...

Answer 1

InnoDB resets the auto_increment field when you restart the database.

When InnoDB restarts, it finds the highest value in the column and then starts from there.

This won't happen in MyISAM because it caches the last incremented id.

Update

This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,

1. Table t1 has an auto-inc primary key.

2. Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.

3. As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.

This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB persistent max value for autoinc columns.

InnoDB will keep track of the maximum value and on restart preserve that max value and start from there.