28

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...

Eae
  • 4,191
  • 15
  • 55
  • 92
  • Possible duplicate of [Stop MySQL Reusing AUTO\_INCREMENT IDs](https://stackoverflow.com/questions/3718229/stop-mysql-reusing-auto-increment-ids) – Jon Schneider Apr 09 '19 at 18:55

5 Answers5

29

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.

Community
  • 1
  • 1
rAjA
  • 899
  • 8
  • 13
  • So the deleted ID's are not reused? – Eae Sep 09 '13 at 05:50
  • No they will not be reused except the cases mentioned above and by @Parham Doustdar – rAjA Sep 09 '13 at 05:54
  • The case above seems to say that the last id needs to be deleted. Does this have to be done manually by query? – Eae Sep 09 '13 at 05:55
  • 1
    If you are worried about InnoDB loses auto_increment track when restarts, you can follow one of the approach mentioned here http://dba.stackexchange.com/questions/16602/prevent-reset-of-auto-increment-id-in-innodb-database-after-server-restart – rAjA Sep 09 '13 at 06:15
  • I thought it was like this also, but our database constantly has new entries with the primary key lower than the highest one, where we previously deleted an entry so I'm confused.. – NaturalBornCamper Jun 26 '17 at 08:45
  • 1
    According above, if delete last 10 rows in InnoDB then restart. These ids of last 10 rows will be reused. – Hao Jan 14 '19 at 08:23
10

When you have a primary key field that is also auto_increment, no matter how many rows (or in what order) are removed, the ids that no longer exist are not used again, and the field is incremented continuously for each row.

However, when your primary key consists of multiple fields (E.G. an auto_increment field and another field), the auto_increment field is reused, and only when the deleted id is the last id. What this means is if you have values like 1, 2, 3, 4, and 5, and you remove the row with the field value of 5, the next row will have an id of 5. However, if you remove the row with the id of 2, this will, again, not be used, and the next row will have an id of 6.

Parham Doustdar
  • 2,019
  • 3
  • 22
  • 41
2

As described with the InnoDB engine the last PK utilised will be reused if the row is deleted prior to a reboot as it is not cached. If this PK is a Foreign Key (FK) in another table problems can arise (FK hell). This is how I discovered the problem when a little old lady shot up to 195 cm(!) as the deleted person's additional data was picked up. The work around for this is either to implement 'ON DELETE CASCADE' for the child tables or (not my preferred option) to code around this issue.

OldSteve
  • 588
  • 1
  • 4
  • 13
0

As mentioned in answer :

InnoDB resets the auto_increment field when you restart the database.

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

and this behavior could lead to Foreign key problem.

Fortunately from MySQL 8.0.0 it will be fixed. More info:

Bug #199 Innodb autoincrement stats los on restart

WL#6204: InnoDB persistent max value for autoinc columns

See BUG#199 on MySQL bugs.

Currently InnoDB does the following when a table is opened: SELECT MAX(c) FROM t; where c is the AUTOINC column name. This step is used to initialise the column's next autoinc value and allocation of autoinc values starts from this point. InnoDB also does this when it executes 'ALTER TABLE AUTO_INCREMENT=N;'.

...

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

If the table is of InnoDB type, then the deleted ids are reused in certain scenarios. Here is what I did to test it:

  1. Table user has id column as primary key, with auto_increment set

    CREATE TABLE user ( id bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), ) ENGINE=InnoDB AUTO_INCREMENT=2108 DEFAULT CHARSET=latin1;

  2. My createUser API call, creates a entry in user table. Lets say user with id = 1 was created.

  3. Call deleteUser API which just deletes row from user table.

  4. Stop and start the database

  5. Call createUserAPI call again. It creates user with same id (id = 1).

So, it does uses the deleted id, if the server is restarted. I got this issue because I had another table called user_rules which stored user_id in a column, but it did not have FK reference to user table (which was wrong). Another issue was that when user was getting deleted, entry from user_rules table was not getting deleted and since it had no FK set, db also didn't complain. And when server got restarted, user rules were messed up!

user1270392
  • 2,981
  • 4
  • 21
  • 25