[Django] #30023: SQLite schema editor can cause table corruption if unsed within a transaction since Django 2.0
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: Simon Charette
Charette |
Type: Bug | Status: assigned
Component: | Version: 2.0
Migrations |
Severity: Release | Keywords:
blocker |
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
From Django 2.0+ SQLite schema editor requires foreign key constraint
checking to be disabled before performing any operations to make sure
foreign keys are not left pointing at `__old` tables when performing
operations requiring table rebuilds.
Since [https://sqlite.org/foreignkeys.html#fk_enable SQLite prevents
foreign key constraint checking from being disabled within a transaction]
`SchemaEditor(atomic=True).__enter__()` has no choice but to disable
foreign key constraints **before** opening the transaction meant to ensure
atomic DDL operations.
One edge case that `SchemaEditor` doesn't account for though is that the
it might contextually be used within an already opened transaction that
would prevent foreign key constraints from being effectively disabled and
result in silent referent table corruption when an operation requiring a
table is rebuild id performed.
In order to prevent this from happening `SchemaEditor().__enter__()`
should ensure foreign key constraint checks are effectively disabled after
requesting it and error out if it's not the case.
This assertion should be more adequate than preventing schema editor from
being used in a transaction altogether as disabling constraint checks
before opening a transaction works just fine as well.
For example
{{{#!python
with transaction.atomic():
call_command('migrate')
}}}
Just has to be converted to
{{{#!python
with connection.constraint_checks_disabled(), transaction.atomic():
call_command('migrate')
}}}
And work just fine.
''This is was originally reported in #29182 but was hijacked to deal with
a SQLite 3.26 issue with similar symptoms and can be reproduced in a
[https://github.com/ezaquarii/django-sqlite-migration-bug test project].
--
Ticket URL: <https://code.djangoproject.com/ticket/30023>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:1>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Severity: Release blocker | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:2>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Severity: Release blocker | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
From Django 2.0+ SQLite schema editor requires foreign key constraint
checking to be disabled to make sure foreign keys are not left pointing at
For example
And work just fine.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:3>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Severity: Release blocker | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Muflone (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:4>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Severity: Release blocker | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:5>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Component: Migrations | Version: 2.0
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"315357ad25a6590e7f4564ec2e56a22132b09001" 315357ad]:
{{{
#!CommitTicketReference repository=""
revision="315357ad25a6590e7f4564ec2e56a22132b09001"
Fixed #30023 -- Prevented SQLite schema alterations while foreign key
checks are enabled.
Prior to this change foreign key constraint references could be left
pointing
at tables dropped during operations simulating unsupported table
alterations
because of an unexpected failure to disable foreign key constraint checks.
SQLite3 does not allow disabling such checks while in a transaction so
they
must be disabled beforehand.
Thanks ezaquarii for the report and Carlton and Tim for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:6>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Migrations | Version: 2.0
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette <charette.s@…>):
In [changeset:"7cf9d15cf80df4ed9a1179bc457ff2d03b5e5cb3" 7cf9d15c]:
{{{
#!CommitTicketReference repository=""
revision="7cf9d15cf80df4ed9a1179bc457ff2d03b5e5cb3"
[2.1.x] Fixed #30023 -- Prevented SQLite schema alterations while foreign
key checks are enabled.
Prior to this change foreign key constraint references could be left
pointing
at tables dropped during operations simulating unsupported table
alterations
because of an unexpected failure to disable foreign key constraint checks.
SQLite3 does not allow disabling such checks while in a transaction so
they
must be disabled beforehand.
Thanks ezaquarii for the report and Carlton and Tim for the review.
Backport of 315357ad25a6590e7f4564ec2e56a22132b09001 from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:7>
Django
transaction since Django 2.0
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: closed
Component: Migrations | Version: 2.0
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette <charette.s@…>):
In [changeset:"ecece1b22e35865cee429e239bcdd19f01925c9d" ecece1b2]:
{{{
#!CommitTicketReference repository=""
revision="ecece1b22e35865cee429e239bcdd19f01925c9d"
[2.0.x] Fixed #30023 -- Prevented SQLite schema alterations while foreign
key checks are enabled.
Prior to this change foreign key constraint references could be left
pointing
at tables dropped during operations simulating unsupported table
alterations
because of an unexpected failure to disable foreign key constraint checks.
SQLite3 does not allow disabling such checks while in a transaction so
they
must be disabled beforehand.
Thanks ezaquarii for the report and Carlton and Tim for the review.
Backport of 315357ad25a6590e7f4564ec2e56a22132b09001 from master.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/30023#comment:8>