#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.
