id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29182	SQLite database migration breaks ForeignKey constraint, leaving <table_name>__old in db schema	ezaquarii	nobody	"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."	Uncategorized	new	Migrations	2.0	Normal		sqlite migration		Unreviewed	0	0	0	0	0	0