Opened 3 years ago

Last modified 4 months ago

#24424 new Bug

Removing a model's last field results in SQL syntax error

Reported by: Adam Hayward Owned by:
Component: Migrations Version: 1.7
Severity: Normal Keywords:
Cc: Markus Holtermann, direx Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by Adam Hayward)

Edit: pull-request with fix here https://github.com/django/django/pull/4223

On migrating an empty, abstract model, the method

django.db.backends.sqlite3.schema.DatabaseSchemaEditor._remake_table()

tries to execute the following SQL query:

INSERT INTO "companies_department__new" () SELECT FROM "companies_department";

The correct SQL would be:

INSERT INTO "companies_department__new" SELECT * FROM "companies_department";

This appears to be the same problem specified in this ticket from South: http://south.aeracode.org/ticket/570 (see comment #15 at the bottom).

Will provide the patch to fix this shortly. Any indication of how to create a test case would be appreciated.

The traceback raised is:

Traceback (most recent call last):
  File "manage.py", line 12, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/test.py", line 50, in run_from_argv
    super(Command, self).run_from_argv(argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/test.py", line 71, in execute
    super(Command, self).execute(*args, **options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/test.py", line 88, in handle
    failures = test_runner.run_tests(test_labels)
  File "/usr/local/lib/python2.7/site-packages/django/test/runner.py", line 147, in run_tests
    old_config = self.setup_databases()
  File "/usr/local/lib/python2.7/site-packages/django/test/runner.py", line 109, in setup_databases
    return setup_databases(self.verbosity, self.interactive, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/django/test/runner.py", line 299, in setup_databases
    serialize=connection.settings_dict.get("TEST", {}).get("SERIALIZE", True),
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/creation.py", line 377, in create_test_db
    test_flush=True,
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 115, in call_command
    return klass.execute(*args, **defaults)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 161, in handle
    executor.migrate(targets, plan, fake=options.get("fake", False))
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 68, in migrate
    self.apply_migration(migration, fake=fake)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 102, in apply_migration
    migration.apply(project_state, schema_editor)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/migration.py", line 108, in apply
    operation.database_forwards(self.app_label, schema_editor, project_state, new_state)
  File "/usr/local/lib/python2.7/site-packages/django/db/migrations/operations/fields.py", line 84, in database_forwards
    schema_editor.remove_field(from_model, from_model._meta.get_field_by_name(self.name)[0])
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 196, in remove_field
    self._remake_table(model, delete_fields=[field])
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 145, in _remake_table
    self.quote_name(model._meta.db_table),
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/schema.py", line 103, in execute
    cusror.execute(sql, params)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cusror.execute(sql, params)
  File "/usr/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cusror.execute(sql, params)
  File "/usr/local/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 488, in execute
    return Database.Cusror.execute(self, query, params)
django.db.utils.OperationalError: near ")": syntax error

Change History (34)

comment:1 Changed 3 years ago by Adam Hayward

Here's the two commits that fix it:

https://github.com/adnam/django/commit/69575fdae253e7bd0a3338db3b2ee9c31bbb259a
https://github.com/adnam/django/commit/57c88c38e627c26df7ec9bd16040905193e7a449

I'd like to create a unit test before submitting a pull request, but not sure where the test should go in the tests directory - any pointers gratefully received.

Last edited 3 years ago by Adam Hayward (previous) (diff)

comment:2 Changed 3 years ago by Adam Hayward

Has patch: set
Needs tests: set

comment:3 Changed 3 years ago by Adam Hayward

Owner: changed from nobody to Adam Hayward
Status: newassigned

comment:4 Changed 3 years ago by Adam Hayward

Description: modified (diff)

comment:5 Changed 3 years ago by Tim Graham

tests/schema might be an appropriate location

comment:6 Changed 3 years ago by Adam Hayward

I fixed the bug and created a unit test which passes (I ran the test with sqlite - it was the sqlite schema editor which had the a small bug). I have created a pull-request containing the changes: https://github.com/django/django/pull/4223

Please let me know if anything else required from my side.

Cheers, Adam

comment:7 Changed 3 years ago by Adam Hayward

Description: modified (diff)

comment:8 Changed 3 years ago by Tim Graham

Needs tests: unset
Triage Stage: UnreviewedAccepted

comment:9 Changed 3 years ago by Adam Hayward

Please let me know if anything more is needed from my side.

comment:10 Changed 3 years ago by Markus Holtermann

Component: MigrationsDatabase layer (models, ORM)
Needs documentation: set
Patch needs improvement: set
Summary: Migrating an 'empty' model with SQLite gives an SQL syntax errorRemoving a model's last field results in SQL syntax error on SQLite
Version: master1.7

Although this bug manifests using migrations, the underlying problem is inside the schema editor for SQLite3. Thus I'm changing the component.

The bug is already in 1.7 as part of a new feature and would have prevented a release back then.

Pleae see the PR for notes regarding the patch itself.

Version 0, edited 3 years ago by Markus Holtermann (next)

comment:11 Changed 3 years ago by Markus Holtermann

Component: Database layer (models, ORM)Migrations
Keywords: sqlite3 migrations removed
Needs tests: set

Ok, the failing test on MySQL shows its non-standard behavior again. And also shows that the fundamental problem is not just the SQLite backend, but also the autodetector that generates models without any fields. This is not a valid model and thus shouldn't be generated by the migrations (and hence the migration autodetector).

I don't have a patch for that ready, but I can verify the problem. Fixing only the SQLite schema editor won't do it.

comment:12 Changed 3 years ago by Markus Holtermann

Initial models and migration:

# models.py
from django.db import models

class Thing(models.Model):
    pass

class Blob(Thing):
    pass


# 0001_initial.py
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Thing',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
            ],
        ),
        migrations.CreateModel(
            name='Blob',
            fields=[
                ('thing_ptr', models.OneToOneField(auto_created=True, parent_link=True, primary_key=True, serialize=False, to='app_a.Thing')),
            ],
            bases=('app_a.thing',),
        ),
    ]

Making Thing inherit a new model Bar:

# models.py
from django.db import models

class Bar(models.Model):
    pass

class Thing(Bar):
    pass

class Blob(Thing):
    pass


# 0002_auto.py
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

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

    operations = [
        migrations.CreateModel(
            name='Bar',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
            ],
        ),
        migrations.RemoveField(
            model_name='thing',
            name='id',
        ),
        migrations.AddField(
            model_name='thing',
            name='bar_ptr',
            field=models.OneToOneField(auto_created=True, default=0, parent_link=True, primary_key=True, serialize=False, to='app_a.Bar'),
            preserve_default=False,
        ),
    ]
Operations to perform:
  Target specific migration: 0002_auto_20150307_1707, from app_a
Running migrations:
  Rendering model states... DONE
  Applying app_a.0002_auto_20150307_1707...

('CREATE TABLE `app_a_bar` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY)', None)
('SELECT engine FROM information_schema.tables WHERE table_name = %s', ['app_a_bar'])
('ALTER TABLE `app_a_thing` DROP COLUMN `id` CASCADE', [])

Traceback (most recent call last):
  File "/home/markus/Coding/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/markus/Coding/django/django/db/backends/mysql/base.py", line 125, in execute
    return self.cursor.execute(query, args)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 219, in execute
    self.errorhandler(self, exc, value)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/connections.py", line 38, in defaulterrorhandler
    raise errorvalue
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 205, in execute
    r = self._query(query)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 372, in _query
    rowcount = self._do_query(q)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 336, in _do_query
    db.query(q)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/connections.py", line 282, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1090, "You can't delete all columns with ALTER TABLE; use DROP TABLE instead")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/markus/Coding/django/django/core/management/__init__.py", line 330, in execute_from_command_line
    utility.execute()
  File "/home/markus/Coding/django/django/core/management/__init__.py", line 322, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/markus/Coding/django/django/core/management/base.py", line 347, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/markus/Coding/django/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/markus/Coding/django/django/core/management/commands/migrate.py", line 195, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "/home/markus/Coding/django/django/db/migrations/executor.py", line 94, in migrate
    self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)
  File "/home/markus/Coding/django/django/db/migrations/executor.py", line 131, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/markus/Coding/django/django/db/migrations/migration.py", line 111, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/home/markus/Coding/django/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 "/home/markus/Coding/django/django/db/backends/base/schema.py", line 441, in remove_field
    self.execute(sql)
  File "/home/markus/Coding/django/django/db/backends/base/schema.py", line 107, in execute
    cursor.execute(sql, params)
  File "/home/markus/Coding/django/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/markus/Coding/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/markus/Coding/django/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/markus/Coding/django/django/utils/six.py", line 658, in reraise
    raise value.with_traceback(tb)
  File "/home/markus/Coding/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/markus/Coding/django/django/db/backends/mysql/base.py", line 125, in execute
    return self.cursor.execute(query, args)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 219, in execute
    self.errorhandler(self, exc, value)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/connections.py", line 38, in defaulterrorhandler
    raise errorvalue
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 205, in execute
    r = self._query(query)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 372, in _query
    rowcount = self._do_query(q)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/cursors.py", line 336, in _do_query
    db.query(q)
  File "/home/markus/.venvs/django-dev-py3/lib/python3.4/site-packages/MySQLdb/connections.py", line 282, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1090, "You can't delete all columns with ALTER TABLE; use DROP TABLE instead")

comment:13 Changed 3 years ago by Adam Hayward

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:14 Changed 3 years ago by Tim Graham

Cc: Markus Holtermann added
Triage Stage: Ready for checkinAccepted

Please don't mark your own patches RFC (that's done by the person who reviews the patch). I didn't understand if Markus's concern is still valid, but I'm going to leave the ticket on the review queue so he can respond.

comment:15 Changed 3 years ago by Adam Hayward

Ah, I see. I was following the triage workflow guidelines which appear to suggest that Committers (core developers) can mark a ticket as fixed, and Ticket Triagers ("anyone in the Django community") can do everything else.

https://docs.djangoproject.com/en/dev/_images/triage_process.svg

comment:16 Changed 3 years ago by Tim Graham

Anyone is welcome to review patches and mark the ticket as RFC, but you shouldn't review your own patches. Happy to review a doc patch if this could be emphasized somewhere around there.

comment:17 Changed 3 years ago by Tim Graham

Patch needs improvement: set

comment:18 Changed 3 years ago by Adam Hayward

Owner: Adam Hayward deleted
Status: assignednew

comment:19 Changed 3 years ago by Jay Wineinger

I took a stab at fixing this by adding a simple counter to prevent the last field from being removed when deleting a model. https://github.com/django/django/pull/4487

comment:20 Changed 3 years ago by Markus Holtermann

Hi jwineinger, thanks for taking a shot at this. I've been playing around with that idea as well, though I don't see how your solution can solve the problem of replacing primary keys, i.e. changing the OneToOneField of a multi table inheritance into a AutoField. Furthermore, it is not about a DeleteModel operation following a RemoveField. That could just remove that field, I think.

The last half-done idea I had was something along those lines:

  1. dropping the old field's constraints (primary key, foreign key, ...)
  2. adding the new field
  3. adding the new field's pk constraint
  4. removing the old field

Though I have no idea how this is going to work out, if at all.

comment:21 Changed 3 years ago by Jay Wineinger

I admit that wasn't the use case I was trying to fix. It seems that there are multiple situation where migrations create states with zero columns in a table. I don't see the relevance in what you describe to the problem I was having, so perhaps they are separate symptoms of this same problem.

When you say "it is not about a DeleteModel operation following a RemoveField. That could just remove that field", what do you mean?

Should the approach be to change the auto-detector to not produce states with zero columns, or should we try and handle that somewhere else?

comment:22 in reply to:  21 Changed 3 years ago by Markus Holtermann

Replying to jwineinger:

When you say "it is not about a DeleteModel operation following a RemoveField. That could just remove that field", what do you mean?

If you have RemoveField('mymodel', 'myfield') followed by a DeleteModel('MyModel') operation, I can't construct a case where Django would end up constructing those two operations automatically where the RemoveField would create a model without fields. A single DeleteModel operation should be sufficient to drop the table and all its constraints.

Should the approach be to change the auto-detector to not produce states with zero columns, or should we try and handle that somewhere else?

Giving this issue more thoughts, I think the only way to prevent the autodetector to construct states with models without fields is making the AlterField operation smarter. Furthermore, when RemoveField would drop the last field on a model, an AlterField operation must be used that then can take care of the steps I outlined in comment 20. This is only necessary for primary keys, though, I think. All other cases can be fixed by first adding the new field and then removing the old field.

comment:23 Changed 3 years ago by Jay Wineinger

The case below is nearly exactly the same one I ran into. But more generally, I think any model that only has relation fields will have this problem when deleted due to the way generate_deleted_models works in the auto-detector.

# models.py
from django.db import models

class Thing(models.Model):
    pass

class Blob(Thing):
    pass


# 0001_initial.py
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Thing',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
            ],
        ),
        migrations.CreateModel(
            name='Blob',
            fields=[
                ('thing_ptr', models.OneToOneField(auto_created=True, parent_link=True, primary_key=True, serialize=False, to='app_a.Thing')),
            ],
            bases=('app_a.thing',),
        ),
    ]

Removing the Blob model

# models.py
from django.db import models

class Thing(models.Model):
    pass


# 0002_auto.py
# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

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

    operations = [
        migrations.RemoveField(
            model_name='blob',
            name='thing_ptr',
        ),
        migrations.DeleteModel(
            name='Blob',
        ),
    ]
./manage.py migrate
Operations to perform:
  Synchronize unmigrated apps: staticfiles, messages
  Apply all migrations: auth, admin, thing, sessions, contenttypes
Synchronizing apps without migrations:
  Creating tables...
    Running deferred SQL...
  Installing custom SQL...
Running migrations:
  Rendering model states... DONE
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... 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 sessions.0001_initial... OK
  Applying thing.0001_initial... OK
  Applying thing.0002_auto_20150415_0113...Traceback (most recent call last):
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/sqlite3/base.py", line 318, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: near ")": syntax error

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
    utility.execute()
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/core/management/__init__.py", line 330, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/core/management/base.py", line 390, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/core/management/base.py", line 441, in execute
    output = self.handle(*args, **options)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 221, in handle
    executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/migrations/executor.py", line 110, in migrate
    self.apply_migration(states[migration], migration, fake=fake, fake_initial=fake_initial)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/migrations/executor.py", line 147, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/migrations/migration.py", line 115, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/jawineinger/venvs/test/lib/python3.4/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 "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/sqlite3/schema.py", line 194, in remove_field
    self._remake_table(model, delete_fields=[field])
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/sqlite3/schema.py", line 144, in _remake_table
    self.quote_name(model._meta.db_table),
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 107, in execute
    cursor.execute(sql, params)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/utils.py", line 97, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/utils/six.py", line 658, in reraise
    raise value.with_traceback(tb)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/jawineinger/venvs/test/lib/python3.4/site-packages/django/db/backends/sqlite3/base.py", line 318, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: near ")": syntax error
Last edited 3 years ago by Jay Wineinger (previous) (diff)

comment:24 Changed 3 years ago by Jay Wineinger

Hrm, seems my patch to omit a RemoveField will probably mess up reverse migrations.

comment:25 Changed 2 years ago by Adam Hayward

I notice this bug is still open after 4 months. My feeling is that reproducing it by creating a rather convoluted series of migrations is rather the wrong way to go about it. The error is quite simple as I explained earlier, and the fix is just a 2-line change in db/backends/sqlite3/schema.py. The bug only affects the sqlite schema editor as it uniquely deletes then re-creates tables upon migrations. Reproducing the error via migrations is rather like eating sushi with 10m-long chopsticks, since the bug is several layers down the internal API. Just my tuppence worth.

comment:26 Changed 2 years ago by Ian Lee

Edit (2015-08-24):

  1. I updated to Django 1.7.10 and same issue.
  2. I was able to work around this by removing the migrations.RemoveField operation and keeping on the migrations.DeleteModel operation in my migration file.

---

Still seeing this issue in Django 1.7.9 (Python 2.7.6)

  Applying foo.0005_auto_20150819_1819...Traceback (most recent call last):
  File "./manage.py", line 31, in <module>
    execute_from_command_line(sys.argv)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 161, in handle
    executor.migrate(targets, plan, fake=options.get("fake", False))
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 68, in migrate
    self.apply_migration(migration, fake=fake)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 102, in apply_migration
    migration.apply(project_state, schema_editor)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/migrations/migration.py", line 108, in apply
    operation.database_forwards(self.app_label, schema_editor, project_state, new_state)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/migrations/operations/fields.py", line 84, in database_forwards
    schema_editor.remove_field(from_model, from_model._meta.get_field_by_name(self.name)[0])
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 197, in remove_field
    self._remake_table(model, delete_fields=[field])
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/sqlite3/schema.py", line 146, in _remake_table
    self.quote_name(model._meta.db_table),
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/schema.py", line 111, in execute
    cursor.execute(sql, params)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 81, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/ianlee1521/.virtualenvs/bar/local/lib/python2.7/site-packages/django/db/backends/sqlite3/base.py", line 485, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: near ")": syntax error

Last edited 2 years ago by Ian Lee (previous) (diff)

comment:27 Changed 2 years ago by Chid Gilovitz

I am also hitting this bug in Django 1.8.4, Python 3.4.3, while running a migration to remove a model's fields and then delete the model. I worked around the problem after reading this bug report and manually removing the migrations.RemoveField command before the migrations.DeleteModel command.

comment:28 Changed 2 years ago by Tim Graham

#25690 is a duplicate.

comment:29 Changed 2 years ago by Simon Charette

Summary: Removing a model's last field results in SQL syntax error on SQLiteRemoving a model's last field results in SQL syntax error

Note that the issue also exists for MySQL.

comment:30 Changed 2 years ago by Tim Graham

#25793 is a duplicate.

comment:31 in reply to:  11 Changed 19 months ago by Mansour Behabadi

Replying to MarkusH:

Ok, the failing test on MySQL shows its non-standard behavior again. And also shows that the fundamental problem is not just the SQLite backend, but also the autodetector that generates models without any fields. This is not a valid model and thus shouldn't be generated by the migrations (and hence the migration autodetector).

I don't have a patch for that ready, but I can verify the problem. Fixing only the SQLite schema editor won't do it.

I agree with this and want an agreed upon solution to be reached so someone can work on it. The only way I can think to get around this is for the autodetector to add an extra temporary field before it removes the last one, and then remove the temporary one when another field is added (in case of renaming a PK). I don't know of the other cases, but if the issue is empty table, then this should do it.

comment:32 Changed 11 months ago by Tim Graham

#27746 looks like a related issue.

Last edited 11 months ago by Tim Graham (previous) (diff)

comment:33 Changed 11 months ago by Tim Graham

As noted in #27764 (closed as a duplicate), the fix should also apply to the reverse operations.

comment:34 Changed 4 months ago by direx

Cc: direx added
Note: See TracTickets for help on using tickets.
Back to Top