Opened 4 years ago

Closed 23 months ago

Last modified 23 months ago

#16992 closed Cleanup/optimization (fixed)

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

Reported by: kent@… Owned by: kedmiston
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 4 years ago by ramiro

  • 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 4 years ago by aaugustin

  • Component changed from Database layer (models, ORM) to Documentation
  • Triage Stage changed from Unreviewed to Accepted
  • Type changed from Bug to Cleanup/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 3 years ago by ramiro

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

comment:4 Changed 2 years ago by akaariai

  • Easy pickings set

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

comment:5 Changed 2 years ago by timo

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

comment:6 Changed 23 months ago by kedmiston

  • Owner changed from nobody to kedmiston
  • Status changed from new to assigned

comment:7 Changed 23 months ago by kedmiston

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 23 months ago by timo

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 23 months ago by timo

  • Resolution set to fixed
  • Status changed from assigned to closed

comment:10 Changed 23 months 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 23 months 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