MySQL InnoDB AUTO_INCREMENT bug (next value to use forgotten at restart)

[kent@…]

I recently ran into problems with Django's automatic id field.

When the latest added object in a table was deleted, a new object created
some time later reused the same id value as the deleted object.

It turned out that this was due to the fact that I had restarted the
MySQL server between the deletion and the creation, and the fact
that the InnoDB backend does not remember the AUTO_INCREMENT value
when restarted (instead recreating it as "max(id)+1").

See for example ​http://bugs.mysql.com/bug.php?id=727

I guess there is not much that Django can do to fix this, but I think
the MySQL/InnoDB documentation should mention this peculiarity.

[Ramiro Morales]

#10164 reports similar behavior with sqlite3, but it is controllable with DDL by adding a ' AUTOINCREMENT' suffix to the AutoField's DB field creation code.

[Aymeric Augustin]

The MySQL devs say:

This behavior is a documented feature which will be fixed in the future at the same time a fast COUNT(*) is introduced to InnoDB.

That was in 2003; I'm not holding my breath. And I don't think we have to document every bug in MySQL.

That said, since it was decided for #10164 to enforce non-reusable primary keys, in this case, it's worth adding a paragraph to the MySQL notes.

[Ramiro Morales]

See also #16961 for a related MySQL particularity regarding AUTO_INCREMENT.

[Anssi Kääriäinen]

Adding a docs note about this should be easy to do.

[Tim Graham]

It looks like this is the MySQL ticket for this issue: ​http://bugs.mysql.com/bug.php?id=199

[Keith Edmiston]

Considering adding the following to the Databases.txt:

"InnoDB autoincrement counter is lost on a MySQL restart because it does not remember the AUTO_INCREMENT value, instead recreating it as "max(id)+1". This may result in an inadvertant reuse of a previous value."

[Tim Graham]

Sounds good. I'd add it after "It's probably the best choice at this point."

However, note that the the InnoDB autoincrement counter is lost on a MySQL
restart because it does not remember the ``AUTO_INCREMENT`` value, instead
recreating it as "max(id)+1". This may result in an inadvertent reuse of
:class:`~django.db.models.AutoField` values.

[Tim Graham]

Fixed in c54fa1a7bc365fec79d4971bf22d5ad2799fde67.

[Tim Graham <timograham@…>]

In a357c854c91b513e3713f2eb72e9742d49c2e701:

[1.6.x] Fixed #16992 -- Added InnoDB warning regarding reuse of AUTO_INCREMENT values.

Thanks kent at nsc.liu.se for the report.

Backport of c54fa1a7bc from master

[Tim Graham <timograham@…>]

In 37587624bf79490e97da1bd04547cdce8b77b9fb:

[1.5.x] Fixed #16992 -- Added InnoDB warning regarding reuse of AUTO_INCREMENT values.

Thanks kent at nsc.liu.se for the report.

Backport of c54fa1a7bc from master