Changes between Version 3 and Version 4 of Ticket #29182


Ignore:
Timestamp:
Mar 3, 2018, 2:20:30 PM (6 years ago)
Author:
ezaquarii
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #29182 – Description

    v3 v4  
    11SQLite 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.
    22
    3 This happens only when running SQLite DB migrations from management command.
     3This happens only when running SQLite DB migrations from management command under transaction.
    44Running migrations via `./manage.py migrate` does not cause any issues.
    55
    66This is the demonstration:
    7 https://github.com/ezaquarii/openvpn-at-home/tree/sqlite-migration-bug
     7https://github.com/ezaquarii/django-sqlite-migration-bug
    88
    99Steps to reproduce the bug
    10 1. build the project with `make devel`
     101. run ./bootstrap.sh
     112. from import app.models import *
     123. Bravo.objects.create()
     134. SQL cannot be executed due to missing table `main.app_alpha__old`
    1114
    12 2. enter Python virtualenv by `source backend/env/bin/activate` and enter `backend` subdirectory
    13 
    14 3. run `./manage.py bootstrap` to initialize the database
    15 
    16 4. bootstrap command runs db migration by calling `call_command('migrate')` in `openvpnathome/apps/management/management/commands/bootstrap.py:37`:
    17     - `openvpn_server` table is created
    18     - `openvpn_client` table is created; it uses foreign key to `openvpn_server`
    19     -  another `openvpn_server` migration is run, adding dummy field
    20     - `openvpn_server` table is renamed to `openvpn_server__old`
    21     - `openvpn_client` foreign key is re-linked to `openvpn_server__old`
    22     - new table `openvpn_server` is created and data from `openvpn_server__old` is copied with applied alterations
    23     - `openvpn_server__old` is dropped
    24     - `openvpn_client` still references `openvpn_sever__old` - ForeignKey constraint is **not** updated and DB is left in unusable state
    25 6. try creating `Client` object in django shell - SQL cannot be executed due to missing table
     15    - `app/management/commands/bootstrap.py:9`: run the migration
     16    - `alpha` table is created
     17    - `bravo` table is created; it uses foreign key to `alpha`
     18    -  another `alpha` migration is run, adding dummy field
     19    - `alpha` table is renamed to `alpha__old`
     20    - `bravo` foreign key is re-linked to `alpha__old`
     21    - new table `alpha` is created and data from `alpha__old` is copied with applied alterations
     22    - `alpha__old` is dropped
     23    - `bravo` still references `alpha__old` - ForeignKey constraint is **not** updated and DB is left in unusable state
    2624
    2725
    28 Exact commands (available in provided `bug_repro.sh` script):
    2926{{{
    30 cd backend
    31 make devel
    32 source ./env/bin/activate
    33 ./manage.py bootstrap
    34 
    35 echo '
    36 Now type that:
    37 
    38 ./manage.py shell
    39 In [1]: from openvpnathome.apps.openvpn.models import Server, Client
    40 
    41 In [2]: Client.objects.create()
    42 
    43 ... snip ...
    44 
    45 OperationalError: no such table: main.openvpn_server__old
    46 '
     27CREATE 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);
    4728}}}
    4829
    49 Exported database schema clearly references deleted `openvpn_server__old` table:
    50 
    51 {{{
    52 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);
    53 }}}
    54 
    55 Please refer to `db_schema.sql` file placed in the `backend` subdirectory.
    56 
    5730Tested with Django versions: 2.0.1 and 2.0.2
Back to Top