Failed migration yields to unmigratable app: "OperationalError: (1050, "Table '<name>' already exists")"

[Natalia Bidart]

tl;dr

While applying existing (and valid) migrations to a MySQL database, one of them failed with:

ValueError: The database backend does not accept 0 as a value for AutoField.

and after fixing the migration by hand, re-running migrate yields to this error:

MySQLdb.OperationalError: (1050, "Table 'testapp_modelz' already exists")

I'm reporting this as a bug because it's unclear to the user how they should proceed in this case. If the ticket is found invalid (likely), then perhaps we could consider adding to the error message an extra note on further steps?

My naïve hope, as a user, was that after fixing the migration, a second run of migrate would succeed.

Longer description:

I have Django test project with some test apps in it. I usually switch the database settings to either use sqlite or Postgresql. Recently, I wanted to debug a issue using a MySQL database, so I started one using docker and I ensured I had a valid connection:

$ python -Wall manage.py dbshell
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.33 MySQL Community Server - GPL
...
mysql>

Then, I made sure I had no pending migrations to generate, and immediately after I applied all the migrations:

$ python -Wall manage.py makemigrations
No changes detected

$ python -Wall manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, testapp, ticket_30382
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 admin.0003_logentry_add_action_flag_choices... 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 auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying sessions.0001_initial... OK
  Applying testapp.0001_initial... OK
  Applying testapp.0002_remove_grandchild_age_alter_child_parent_and_more... OK
  Applying testapp.0003_alter_article_authors... OK
  Applying testapp.0004_question_voter_choice... OK
  Applying testapp.0005_product_remove_choice_voters_images_delete_voter... OK
  Applying testapp.0006_modela_modelb... OK
  Applying testapp.0007_modelb_foo... OK
  Applying testapp.0008_rename_foo_modelb__foo... OK
  Applying testapp.0009_modelz_remove_modelb_id_modelb_modelz_ptr...Traceback (most recent call last):
  File "/home/nessita/fellowship/projectfromrepo/manage.py", line 22, in <module>
    main()
  File "/home/nessita/fellowship/projectfromrepo/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/nessita/fellowship/django/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/home/nessita/fellowship/django/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/nessita/fellowship/django/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/nessita/fellowship/django/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/home/nessita/fellowship/django/django/db/migrations/operations/fields.py", line 108, in database_forwards
    schema_editor.add_field(
  File "/home/nessita/fellowship/django/django/db/backends/mysql/schema.py", line 107, in add_field
    super().add_field(model, field)
  File "/home/nessita/fellowship/django/django/db/backends/base/schema.py", line 668, in add_field
    definition, params = self.column_sql(model, field, include_default=True)
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/base/schema.py", line 359, in column_sql
    " ".join(
  File "/home/nessita/fellowship/django/django/db/backends/base/schema.py", line 310, in _iter_column_sql
    default_value = self.effective_default(field)
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/base/schema.py", line 429, in effective_default
    return field.get_db_prep_save(self._effective_default(field), self.connection)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/models/fields/related.py", line 1149, in get_db_prep_save
    return self.target_field.get_db_prep_save(value, connection=connection)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/models/fields/__init__.py", line 957, in get_db_prep_save
    return self.get_db_prep_value(value, connection=connection, prepared=False)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/models/fields/__init__.py", line 2748, in get_db_prep_value
    value = connection.ops.validate_autopk_value(value)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/mysql/operations.py", line 250, in validate_autopk_value
    raise ValueError(
ValueError: The database backend does not accept 0 as a value for AutoField.

I edited the troubling migration by hand, changing the default from 0 to 1, but migrate would no longer work:

$ python -Wall manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, testapp, ticket_30382
Running migrations:
  Applying testapp.0009_modelz_remove_modelb_id_modelb_modelz_ptr...Traceback (most recent call last):
  File "/home/nessita/fellowship/django/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/.virtualenvs/djangodev/lib/python3.11/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/home/nessita/.virtualenvs/djangodev/lib/python3.11/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/nessita/.virtualenvs/djangodev/lib/python3.11/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1050, "Table 'testapp_modelz' already exists")

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

Traceback (most recent call last):
  File "/home/nessita/fellowship/projectfromrepo/manage.py", line 22, in <module>
    main()
  File "/home/nessita/fellowship/projectfromrepo/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/nessita/fellowship/django/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/home/nessita/fellowship/django/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/nessita/fellowship/django/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/nessita/fellowship/django/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/home/nessita/fellowship/django/django/db/migrations/operations/models.py", line 96, in database_forwards
    schema_editor.create_model(model)
  File "/home/nessita/fellowship/django/django/db/backends/base/schema.py", line 451, in create_model
    self.execute(sql, params or None)
  File "/home/nessita/fellowship/django/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/home/nessita/fellowship/django/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/home/nessita/fellowship/django/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/nessita/fellowship/django/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/fellowship/django/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/nessita/.virtualenvs/djangodev/lib/python3.11/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
          ^^^^^^^^^^^^^^^^^^
  File "/home/nessita/.virtualenvs/djangodev/lib/python3.11/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/nessita/.virtualenvs/djangodev/lib/python3.11/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1050, "Table 'testapp_modelz' already exists")

[Simon Charette]

I'm afraid there's little that can be done here on MySQL which doesn't support transactional DDL.

If you have a migration with multiple operations and one of them happen to fail Django has no way to know which operations are applied and which aren't so re-running the migration requires attempting the whole sequence of operations again.

​This is a documented limitation of MySQL.

[Mariusz Felisiak]

I'm sorry but it is support question and Trac is not a support channel.

ValueError: The database backend does not accept 0 as a value for AutoField.

I'm not sure how we could be clearer here. 0 doesn't work for auto increment fields on MySQL without the NO_AUTO_VALUE_ON_ZERO SQL mode.

MySQLdb.OperationalError: (1050, "Table 'testapp_modelz' already exists")

MySQL doesn't support roll back DDL operations in a transaction.

[Natalia Bidart]

Thanks Mariusz, though I wasn't looking for support, I already fixed my DB manually.

I still felt this issue is a paper-cut for Django users and some indication about how to proceed could be valuable for them (and that's the reason why I created this ticket).

[Natalia Bidart]

Replying to Simon Charette:

I'm afraid there's little that can be done here on MySQL which doesn't support transactional DDL.

If you have a migration with multiple operations and one of them happen to fail Django has no way to know which operations are applied and which aren't so re-running the migration requires attempting the whole sequence of operations again.

​This is a documented limitation of MySQL.

Thanks Simon for this answer, it's both attentive and helpful!

Was it ever considered/discussed to add a message after a failed migration pointing the user to the specific docs for further information about the available (or not available) options (depending on the DB)?

[Simon Charette]

Was it ever considered/discussed to add a message after a failed migration pointing the user to the specific docs for further information about the available (or not available) options (depending on the DB)?

Not that I know of. I guess we could conditionally augment the message with a pointer at the docs when the current migration fails to apply and isn't atomic as he same problem can also happen on backends that do support transactional DDL for migrations that explicitly marked as atomic = False.

[David Sanders]

I like the idea of a link to the docs 👍 though I'm not a MySQL user so I have no skin in this game.