#29182: SQLite database migration breaks ForeignKey constraint, leaving <table_name>__old in db schema ----------------------------------+-------------------------------------- Reporter: ezaquarii | Owner: nobody Type: Uncategorized | Status: new Component: Migrations | Version: 2.0 Severity: Normal | Resolution: Keywords: sqlite migration | Triage Stage: Unreviewed Has patch: 0 | Needs documentation: 0 Needs tests: 0 | Patch needs improvement: 0 Easy pickings: 0 | UI/UX: 0 ----------------------------------+--------------------------------------
Old description: > 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 > 6. try creating `Client` object in django shell - SQL cannot be executed > due to missing table > > Exact commands (available in provided `bug_repro.sh` script): > {{{ > make devel > cd backend > 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 New description: 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 6. 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 -- Comment (by ezaquarii): Please check this repository: https://github.com/ezaquarii/django-sqlite-migration-bug {{{ with transaction.atomic(): call_command('migrate') }}} I think this is the issue. It works ok without transaction. -- Ticket URL: <https://code.djangoproject.com/ticket/29182#comment:3> Django <https://code.djangoproject.com/> The Web framework for perfectionists with deadlines. -- You received this message because you are subscribed to the Google Groups "Django updates" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-updates+unsubscr...@googlegroups.com. To post to this group, send email to django-updates@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/067.93a74f8ed3a596dc6b6ddbb58ab0cf5b%40djangoproject.com. For more options, visit https://groups.google.com/d/optout.