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