#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 [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/052.2c20ce961fadeb6c9cd3d18bba23281e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
