[Django] #16992: MySQL InnoDB AUTO_INCREMENT bug (next value to use forgotten at restart)
Django
----------------------------------------------+--------------------
Reporter: kent@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.3
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/16992>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by ramiro):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
#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.
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:1>
Django
Reporter: kent@… | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 1.3
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Changes (by aaugustin):
* component: Database layer (models, ORM) => Documentation
* type: Bug => Cleanup/optimization
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:2>
Django
Reporter: kent@… | Owner: nobody
Component: Documentation | Version: 1.3
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Comment (by ramiro):
See also #16961 for a related MySQL particularity regarding
`AUTO_INCREMENT`.
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:3>
Django
Reporter: kent@… | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 1.3
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
--------------------------------------+------------------------------------
Changes (by akaariai):
* easy: 0 => 1
Comment:
Adding a docs note about this should be easy to do.
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:4>
Django
Reporter: kent@… | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 1.3
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
--------------------------------------+------------------------------------
Comment (by timo):
It looks like this is the MySQL ticket for this issue:
http://bugs.mysql.com/bug.php?id=199
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:5>
Django
Reporter: kent@… | Owner: kedmiston
Type: | Status: assigned
Cleanup/optimization | Version: 1.3
Component: Documentation | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Changes (by kedmiston):
* status: new => assigned
* owner: nobody => kedmiston
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:6>
Django
Reporter: kent@… | Owner: kedmiston
Type: | Status: assigned
Cleanup/optimization | Version: 1.3
Component: Documentation | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (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."
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:7>
Django
Reporter: kent@… | Owner: kedmiston
Type: | Status: assigned
Cleanup/optimization | Version: 1.3
Component: Documentation | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:8>
Django
Reporter: kent@… | Owner: kedmiston
Cleanup/optimization | Version: 1.3
Component: Documentation | Resolution: fixed
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
Fixed in c54fa1a7bc365fec79d4971bf22d5ad2799fde67.
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:9>
Django
Reporter: kent@… | Owner: kedmiston
Type: | Status: closed
Cleanup/optimization | Version: 1.3
Component: Documentation | Resolution: fixed
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"a357c854c91b513e3713f2eb72e9742d49c2e701"]:
{{{
#!CommitTicketReference repository=""
revision="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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:10>
Django
Reporter: kent@… | Owner: kedmiston
Type: | Status: closed
Cleanup/optimization | Version: 1.3
Component: Documentation | Resolution: fixed
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 1 |
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"37587624bf79490e97da1bd04547cdce8b77b9fb"]:
{{{
#!CommitTicketReference repository=""
revision="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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/16992#comment:11>