Trying to migrate in Django 1.9 -- strange SQL error "django.db.utils.OperationalError: near ")": syntax error"
I don't have a clue what's causing this error. It appears to be a bug that there isn't a fix for. Could anyone tell give me a hint as to how I might get around this? It's frustrating me to no end. Thanks.
Operations to perform:
Apply all migrations: admin, contenttypes, optilab, auth, sessions
Running migrations:
Rendering model states... DONE
Applying optilab.0006_auto_20160621_1640...Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line 353, in execute_from_command_line
utility.execute()
File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line 345, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "C:\Python27\lib\site-packages\django\core\management\base.py", line 348, in run_from_argv
self.execute(*args, **cmd_options)
File "C:\Python27\lib\site-packages\django\core\management\base.py", line 399, in execute
output = self.handle(*args, **options)
File "C:\Python27\lib\site-packages\django\core\management\commands\migrate.py", line 200, in handle
executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
File "C:\Python27\lib\site-packages\django\db\migrations\executor.py", line 92, in migrate
self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
File "C:\Python27\lib\site-packages\django\db\migrations\executor.py", line 121, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "C:\Python27\lib\site-packages\django\db\migrations\executor.py", line 198, in apply_migration
state = migration.apply(state, schema_editor)
File "C:\Python27\lib\site-packages\django\db\migrations\migration.py", line 123, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "C:\Python27\lib\site-packages\django\db\migrations\operations\fields.py", line 121, in database_forwards
schema_editor.remove_field(from_model, from_model._meta.get_field(self.name))
File "C:\Python27\lib\site-packages\django\db\backends\sqlite3\schema.py", line 247, in remove_field
self._remake_table(model, delete_fields=[field])
File "C:\Python27\lib\site-packages\django\db\backends\sqlite3\schema.py", line 197, in _remake_table
self.quote_name(model._meta.db_table),
File "C:\Python27\lib\site-packages\django\db\backends\base\schema.py", line 110, in execute
cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\utils.py", line 95, in __exit__
six.reraise(dj_exc_type, dj_exc_value, traceback)
File "C:\Python27\lib\site-packages\django\db\backends\utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "C:\Python27\lib\site-packages\django\db\backends\sqlite3\base.py", line 323, in execute
return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: near ")": syntax error
Here's the contents of 0006_auto_20160621_1640.py
# -*- coding: utf-8 -*-
# Generated by Django 1.9.6 on 2016-06-21 22:40
from __future__ import unicode_literals
from django.db import migrations
class Migration(migrations.Migration):
dependencies = [
('optilab', '0005_test'),
]
operations = [
migrations.RemoveField(
model_name='lasersubstrate',
name='substrate_ptr',
),
migrations.RemoveField(
model_name='waveguidesubstrate',
name='substrate_ptr',
),
migrations.DeleteModel(
name='LaserSubstrate',
),
migrations.DeleteModel(
name='WaveguideSubstrate',
),
]
Here's the SQL produced from running 'python manage.py sqlmigrate optilab 0006'
BEGIN;
--
-- Remove field substrate_ptr from lasersubstrate
--
ALTER TABLE "optilab_lasersubstrate" RENAME TO "optilab_lasersubstrate__old";
CREATE TABLE "optilab_lasersubstrate" ("substrate_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "optilab_substrate" ("id"));
INSERT INTO "optilab_lasersubstrate" () SELECT FROM "optilab_lasersubstrate__old";
DROP TABLE "optilab_lasersubstrate__old";
--
-- Remove field substrate_ptr from waveguidesubstrate
--
ALTER TABLE "optilab_waveguidesubstrate" RENAME TO "optilab_waveguidesubstrate__old";
CREATE TABLE "optilab_waveguidesubstrate" ("substrate_ptr_id" integer NOT NULL PRIMARY KEY REFERENCES "optilab_substrate" ("id"));
INSERT INTO "optilab_waveguidesubstrate" () SELECT FROM "optilab_waveguidesubstrate__old";
DROP TABLE "optilab_waveguidesubstrate__old";
--
-- Delete model LaserSubstrate
--
DROP TABLE "optilab_lasersubstrate";
--
-- Delete model WaveguideSubstrate
--
DROP TABLE "optilab_waveguidesubstrate";
COMMIT;
Answer
This appears to be the line that's causing the errror:
INSERT INTO "optilab_lasersubstrate" () SELECT FROM "optilab_lasersubstrate__old";
You are usually expected to have a list of columns in those parenthesis. Eg INSERT INTO "optilab_lasersubstrate" (col1,col2,etc) however the migration has produced a blank set! Similarly the SELECT FROM portion should read as SELECT col1,col2 FROM. By some strange set of events you appear to have managed to create a table with no columns!!
I see from your migration file that you are anyway dropping this table. So there isn't any reason to struggle with the RemoveField portion. It's code associated with the RemoveField that's causing the error. Change your migration as follows:
class Migration(migrations.Migration):
dependencies = [
('optilab', '0005_test'),
]
operations = [
migrations.DeleteModel(
name='LaserSubstrate',
),
migrations.DeleteModel(
name='WaveguideSubstrate',
),
]