no such table: main.standards_review_standard__old

[khaug]

Hi Guys

We are trying to build this locally, and we are having some problems.

Have you seen this issue before?

`(venv) Keevas-MacBook-Pro:mcf_standard_browse keeva$ python manage.py migrate
Logging started on root for DEBUG
Logging started on root for DEBUG
System check identified some issues:

WARNINGS:
standards_review.MoleculeSpectraCount.molecule: (fields.W342) Setting unique=True on a ForeignKey has the same effect as using a OneToOneField.
HINT: ForeignKey(unique=True) is usually better served by a OneToOneField.
Operations to perform:
Apply all migrations: admin, auth, contenttypes, djcelery, sessions, standards_review
Running migrations:
Applying contenttypes.0001_initial... OK
Applying auth.0001_initial... OK
Applying admin.0001_initial... OK
Applying admin.0002_logentry_remove_auto_add... OK
Applying contenttypes.0002_remove_content_type_name... OK
Applying auth.0002_alter_permission_name_max_length... OK
Applying auth.0003_alter_user_email_max_length... OK
Applying auth.0004_alter_user_username_opts... OK
Applying auth.0005_alter_user_last_login_null... OK
Applying auth.0006_require_contenttypes_0002... OK
Applying auth.0007_alter_validators_add_error_messages... OK
Applying auth.0008_alter_user_username_max_length... OK
Applying djcelery.0001_initial... OK
Applying sessions.0001_initial... OK
Applying standards_review.0001_initial... OK
Applying standards_review.0002_moleculespectracount_view... OK
Applying standards_review.0003_auto_20160616_1528...Traceback (most recent call last):
File "/anaconda3/envs/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
return self.cursor.execute(sql, params)
File "/anaconda3/envs/venv/lib/python3.6/site-packages/django/db/backends/sqlite3/base.py", line 328, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: error in view standards_review_moleculespectracount: no such table: main.standards_review_standard__old

`

Cheers
Ken

[theodev]

@LachlanStuart can you please have a quick look?

[khaug]

Hi, before you spend any time on this, we did install newer versions of quite a few of the libraries/modules referenced in requirements.txt. This may of course have caused the problem. Please hold off for a while whilst I investigate this further. Sorry and thanks!

[LachlanStuart]

@khaug I think you'll still get this issue on the older versions. I did a fresh install with the older versions and ran into the same issue.

I believe the issue is that SQLite treats database views differently to other DBMSs when there are schema changes after the view is created. The moleculespectracount view created in the 0002 migration directly depends on a table that Django later renames then deletes as part of the 0003 migration. In SQLite it seems that the views must still be valid after every schema change, and while it handles the table being renamed, when the table is deleted the view stops being valid and SQLite raises an error.

The quickest workaround is to change the migrations' dependencies so that the view doesn't get created until the rest of the schema is stable, i.e.:

In standards_review/migrations/0002_moleculespectracount_view.py change the dependencies to:

    dependencies = [
        ('standards_review', '0010_renameMCFID_massBankRequisits'),
    ]

In standards_review/migrations/0003_auto_20160616_1528.py change the dependencies to:

    dependencies = [
        ('standards_review', '0001_initial'),
    ]

You'll also have to roll back to the first migration, or just delete db.sqlite, to clear out the view that would have been already created.