[Django] #34546: Failed migration yields to unmigratable app:""OperationalError: (1050, "Table '<name>' already exists")
Django
"Table '<name>' already exists")
------------------------------------------+------------------------
Reporter: Natalia Bidart | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 4.2
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------+------------------------
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")
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34546>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
"Table '<name>' already exists")
Reporter: Natalia Bidart | Owner: nobody
Component: Migrations | Version: 4.2
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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).
--
Ticket URL: <https://code.djangoproject.com/ticket/34546#comment:4>
Django
"Table '<name>' already exists")
--------------------------------+--------------------------------------
Reporter: Natalia Bidart | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 4.2
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+--------------------------------------
Comment (by Natalia Bidart):
Replying to [comment:2 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.
>
> [https://docs.djangoproject.com/en/4.2/topics/migrations/#mysql 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)?
--
Ticket URL: <https://code.djangoproject.com/ticket/34546#comment:5>
Django
"Table '<name>' already exists")
--------------------------------+--------------------------------------
Reporter: Natalia Bidart | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 4.2
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+--------------------------------------
Comment (by 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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/34546#comment:6>
Django
"Table '<name>' already exists")
--------------------------------+--------------------------------------
Reporter: Natalia Bidart | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 4.2
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+--------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34546#comment:7>