SQLite database migration breaks ForeignKey constraint, leaving <table_name>__old in db schema

[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.
Running migrations via ./manage.py migrate does not cause any issues.

This is the demonstration:
​https://github.com/ezaquarii/openvpn-at-home/tree/sqlite-migration-bug

Steps to reproduce the bug

1. build the project with make devel

2. enter Python virtualenv by source backend/env/bin/activate and enter backend subdirectory

3. run ./manage.py bootstrap to initialize the database

4. bootstrap command runs db migration by calling call_command('migrate') in openvpnathome/apps/management/management/commands/bootstrap.py:37:
   • openvpn_server table is created
   • openvpn_client table is created; it uses foreign key to openvpn_server
   • another openvpn_server migration is run, adding dummy field
   • openvpn_server table is renamed to openvpn_server__old
   • openvpn_client foreign key is re-linked to openvpn_server__old
   • new table openvpn_server is created and data from openvpn_server__old is copied with applied alterations
   • openvpn_server__old is dropped
   • openvpn_client still references openvpn_sever__old - ForeignKey constraint is not updated and DB is left in unusable state
5. try creating Client object in django shell - SQL cannot be executed due to missing table

Exact commands (available in provided bug_repro.sh script):

cd backend
make devel
source ./env/bin/activate
./manage.py bootstrap

echo '
Now type that:

./manage.py shell
In [1]: from openvpnathome.apps.openvpn.models import Server, Client

In [2]: Client.objects.create()

... snip ...

OperationalError: no such table: main.openvpn_server__old
'

Exported database schema clearly references deleted openvpn_server__old table:

CREATE TABLE "openvpn_client" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "created" datetime NOT NULL, "name" varchar(64) NOT NULL, "cert_id" integer NOT NULL REFERENCES "x509_cert" ("id") DEFERRABLE INITIALLY DEFERRED, "owner_id" integer NOT NULL REFERENCES "accounts_user" ("id") DEFERRABLE INITIALLY DEFERRED, "server_id" integer NOT NULL REFERENCES "openvpn_server__old" ("id") DEFERRABLE INITIALLY DEFERRED);

Please refer to db_schema.sql file placed in the backend subdirectory.

Tested with Django versions: 2.0.1 and 2.0.2

[ezaquarii]

Bug reproduction steps as shell script

[ezaquarii]

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

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

[ezaquarii]

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.