migration introducing a UUID primary key fails on sqlite3

[karyon]

the migration here (from ​https://github.com/fsr-itse/EvaP/pull/1002/files)

# -*- coding: utf-8 -*-
# Generated by Django 1.11.3 on 2017-07-03 18:31
from __future__ import unicode_literals

from django.db import migrations, models
import uuid

def fill_textanswer_uuid(apps, schema_editor):
    db_alias = schema_editor.connection.alias
    TextAnswer = apps.get_model('evaluation', 'TextAnswer')
    for obj in TextAnswer.objects.using(db_alias).all():
        obj.uuid = uuid.uuid4()
        obj.save()

class Migration(migrations.Migration):
    """ this migration changes a model from a auto-generated id field to a uuid-primary key. """

    operations = [
        migrations.AddField(
            model_name='textanswer',
            name='uuid',
            field=models.UUIDField(null=True),
        ),
        migrations.RunPython(fill_textanswer_uuid, migrations.RunPython.noop),
        migrations.AlterField(
            model_name='textanswer',
            name='uuid',
            field=models.UUIDField(primary_key=False, default=uuid.uuid4, serialize=False, editable=False),
        ),
        migrations.RemoveField('TextAnswer', 'id'),
        migrations.RenameField(
            model_name='textanswer',
            old_name='uuid',
            new_name='id'
        ),
        migrations.AlterField(
            model_name='textanswer',
            name='id',
            field=models.UUIDField(primary_key=True, default=uuid.uuid4, serialize=False, editable=False),
        ),
    ]

fails when running with sqlite3. postgres works fine. when commenting out the last two operations in the migration, it works.

Traceback :

Traceback (most recent call last):
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/utils.py", line 63, in execute
    return self.cursor.execute(sql)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/sqlite3/base.py", line 326, in execute
    return Database.Cursor.execute(self, query)
sqlite3.OperationalError: duplicate column name: id

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/vagrant/.local/lib/python3.4/site-packages/django/core/management/__init__.py", line 363, in execute_from_command_line
    utility.execute()
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/core/management/__init__.py", line 355, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/migrations/operations/fields.py", line 299, in database_forwards
    to_model._meta.get_field(self.new_name),
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 514, in alter_field
    old_db_params, new_db_params, strict)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/sqlite3/schema.py", line 262, in _alter_field
    self._remake_table(model, alter_field=(old_field, new_field))
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/sqlite3/schema.py", line 198, in _remake_table
    self.create_model(temp_model)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 303, in create_model
    self.execute(sql, params or None)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/utils.py", line 63, in execute
    return self.cursor.execute(sql)
  File "/home/vagrant/.local/lib/python3.4/site-packages/django/db/backends/sqlite3/base.py", line 326, in execute
    return Database.Cursor.execute(self, query)
django.db.utils.OperationalError: duplicate column name: id

sql query in question:

CREATE TABLE "evaluation_textanswer" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "reviewed_answer" text NULL, "original_answer" text NOT NULL, "contribution_id" integer NOT NULL REFERENCES "evaluation_contribution" ("id"), "question_id" integer NOT NULL REFERENCES "evaluation_question" ("id"), "state" varchar(2) NOT NULL, "id" char(32) NOT NULL); args=None

so yeah, it obviously tries to create two "id" columns.

full console output with (sqlite)-sql statements: ​https://pastebin.com/r6CF22GJ

[Simon Charette]

I haven't reproduced but the report seems legit given how SQLite's schema editor generates dynamic model to perform table rebuild on ALTERs and how Django automatically generate an id field when one is missing.

Could try reproducing against master as well?

[karyon]

looks the same to me: ​https://pastebin.com/gYgm2ra5

[karyon]

In case anyone else is affected: We found a workaround, that is instead of removing the old id column straight away, first rename it and then delete it at the end of the migration. See ​https://github.com/fsr-itse/EvaP/pull/1216/files

[bcail]

I opened ​a PR.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 593067a:

Fixed #28541 -- Fixed migrations crash when changing primary key on SQLite.