Opened 4 years ago

Last modified 22 months ago

#29182 closed Bug

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

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.
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
  1. enter Python virtualenv by source backend/env/bin/activate and enter backend subdirectory
  1. run ./manage.py bootstrap to initialize the database
  1. 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
  2. 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

Change History (3)

Changed 4 years ago by ezaquarii

Attachment: bug_repro.sh added

Bug reproduction steps as shell script

Changed 4 years ago by ezaquarii

Attachment: db_schema.sql added

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

comment:1 Changed 4 years ago by ezaquarii

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