Opened 5 years ago

Closed 3 years ago

Last modified 3 years ago

#16992 closed Cleanup/optimization (fixed)

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

Reported by: kent@… Owned by: Keith Edmiston
Component: Documentation Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

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.

Change History (11)

comment:1 Changed 5 years ago by Ramiro Morales

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

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

comment:2 Changed 5 years ago by Aymeric Augustin

Component: Database layer (models, ORM)Documentation
Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization

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.

comment:3 Changed 4 years ago by Ramiro Morales

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

comment:4 Changed 3 years ago by Anssi Kääriäinen

Easy pickings: set

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

comment:5 Changed 3 years ago by Tim Graham

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

comment:6 Changed 3 years ago by Keith Edmiston

Owner: changed from nobody to Keith Edmiston
Status: newassigned

comment:7 Changed 3 years ago by 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."

comment:8 Changed 3 years ago by 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.

comment:9 Changed 3 years ago by Tim Graham

Resolution: fixed
Status: assignedclosed

comment:10 Changed 3 years ago by 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

comment:11 Changed 3 years ago by 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

Note: See TracTickets for help on using tickets.
Back to Top