Opened 3 years ago

Last modified 18 months ago

#29182 closed Bug

SQLite database migration breaks ForeignKey constraint, leaving <table_name>__old in db schema — at Version 4

Reported by: ezaquarii Owned by: nobody
Component: Migrations Version: 2.0
Severity: Release blocker Keywords: sqlite migration
Cc: Simon Charette, Florian Apolloner, Adam Goode, Muflone Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by ezaquarii)

SQLite table alteration uses rename and copy method. When running a database migration from Django command (via call_command('migrate')), database is left with references to <table_name>old table that has been dropped after migration.

This happens only when running SQLite DB migrations from management command under transaction.
Running migrations via ./manage.py migrate does not cause any issues.

This is the demonstration:
https://github.com/ezaquarii/django-sqlite-migration-bug

Steps to reproduce the bug

  1. run ./bootstrap.sh
  2. from import app.models import *
  3. Bravo.objects.create()
  4. SQL cannot be executed due to missing table main.app_alpha__old
  • app/management/commands/bootstrap.py:9: run the migration
  • alpha table is created
  • bravo table is created; it uses foreign key to alpha
  • another alpha migration is run, adding dummy field
  • alpha table is renamed to alpha__old
  • bravo foreign key is re-linked to alpha__old
  • new table alpha is created and data from alpha__old is copied with applied alterations
  • alpha__old is dropped
  • bravo still references alpha__old - ForeignKey constraint is not updated and DB is left in unusable state
CREATE TABLE "app_bravo" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "dummy" varchar(10) NOT NULL, "alpha_id" integer NOT NULL REFERENCES "app_alpha__old" ("id") DEFERRABLE INITIALLY DEFERRED);

Tested with Django versions: 2.0.1 and 2.0.2

Change History (6)

Changed 3 years ago by ezaquarii

Attachment: bug_repro.sh added

Bug reproduction steps as shell script

Changed 3 years ago by ezaquarii

Attachment: db_schema.sql added

Database schema after running migrations - see references to temporary openvpn_serverold table

comment:1 Changed 3 years ago by ezaquarii

Description: modified (diff)

comment:2 Changed 3 years ago by Tim Graham

Could you provide a more minimal project that reproduces the issue? In particular, the steps to reproduce shouldn't require installing a bunch of NodeJS packages. Thanks!

comment:3 in reply to:  2 Changed 3 years ago by ezaquarii

Description: modified (diff)

Please check this repository:

https://github.com/ezaquarii/django-sqlite-migration-bug

Please check out bootstrap.py

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

I think this is the issue. It works ok without transaction.

Last edited 3 years ago by ezaquarii (previous) (diff)

comment:4 Changed 3 years ago by ezaquarii

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top