Opened 2 months ago

Closed 2 months ago

Last modified 2 months ago

#30023 closed Bug (fixed)

SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

Reported by: Simon Charette Owned by: Simon Charette
Component: Migrations Version: 2.0
Severity: Release blocker Keywords:
Cc: ezaquarii, Muflone Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Simon Charette)

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 __old tables when performing operations requiring table rebuilds.

Since 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

with transaction.atomic():
    call_command('migrate')

Just has to be converted to

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 test project.

Change History (8)

comment:1 Changed 2 months ago by Simon Charette

Has patch: set

comment:2 Changed 2 months ago by Simon Charette

Summary: SQLite schema editor can cause table corruption if unsed within a transaction since Django 2.0SQLite schema editor can cause table corruption if used within a transaction since Django 2.0

comment:3 Changed 2 months ago by Simon Charette

Description: modified (diff)

comment:4 Changed 2 months ago by Muflone

Cc: Muflone added

comment:5 Changed 2 months ago by Carlton Gibson

Triage Stage: AcceptedReady for checkin

comment:6 Changed 2 months ago by Simon Charette <charette.s@…>

Resolution: fixed
Status: assignedclosed

In 315357ad:

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.

comment:7 Changed 2 months ago by Simon Charette <charette.s@…>

In 7cf9d15c:

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

comment:8 Changed 2 months ago by Simon Charette <charette.s@…>

In ecece1b2:

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

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