#29182: SQLite database migration breaks ForeignKey constraint, leaving
<table_name>__old in db schema
-------------------------------------+-------------------------------------
Reporter: ezaquarii | Owner: nobody
Type: | Status: new
Uncategorized |
Component: | Version: 2.0
Migrations |
Severity: Normal | Keywords: sqlite migration
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29182>
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/052.2c20ce961fadeb6c9cd3d18bba23281e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
