#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
----------------------------------+--------------------------------------
Description changed by ezaquarii:
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):
> {{{
> 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
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 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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29182#comment:4>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.9a985504ad0190cb91037ecbf1f0f4d5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
