Opened 21 months ago

Closed 19 months ago

Last modified 18 months ago

#32503 closed Bug (fixed)

AlterField migration to TextField with default fails on MySQL 8.0.13+

Reported by: Matt Westcott Owned by: Yuekui
Component: Migrations Version: 3.0
Severity: Normal Keywords: mysql default
Cc: Adam Johnson, Matt Westcott Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

On MySQL 8.0.23 with mysqlclient==2.0.3, running a migration with an AlterField operation that changes a field to a TextField with a default value fails with the error: MySQLdb._exceptions.OperationalError: (1101, "BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value").

To reproduce:

On a fresh project configured with a MySQL 8.0.23 database, add a base app with the following definition in models.py:

from django.db import models

class Article(models.Model):
    body = models.CharField(max_length=255, null=True, blank=True)

Run ./manage.py makemigrations

Edit models.py to

from django.db import models

class Article(models.Model):
    body = models.TextField(blank=True)

and re-run ./manage.py makemigrations, entering "" as the default when prompted. This should generate the migration:

from django.db import migrations, models


class Migration(migrations.Migration):

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

    operations = [
        migrations.AlterField(
            model_name='article',
            name='body',
            field=models.TextField(blank=True, default=''),
            preserve_default=False,
        ),
    ]

./manage.py migrate then fails with the output:

System check identified some issues:

WARNINGS:
base.Article: (models.W042) Auto-created primary key used when not defining a primary key type, by default 'django.db.models.AutoField'.
	HINT: Configure the DEFAULT_AUTO_FIELD setting or the BaseConfig.default_auto_field attribute to point to a subclass of AutoField, e.g. 'django.db.models.BigAutoField'.
Operations to perform:
  Apply all migrations: admin, auth, base, contenttypes, sessions
Running migrations:
  Applying base.0001_initial... OK
  Applying base.0002_auto_20210303_1601...Traceback (most recent call last):
  File "/vagrant/libs/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/vagrant/libs/django/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/home/vagrant/.virtualenvs/wagtailmysql/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/vagrant/.virtualenvs/wagtailmysql/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/vagrant/.virtualenvs/wagtailmysql/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1101, "BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value")

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

Traceback (most recent call last):
  File "./manage.py", line 21, in <module>
    main()
  File "./manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/vagrant/libs/django/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/vagrant/libs/django/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/vagrant/libs/django/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/vagrant/libs/django/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/vagrant/libs/django/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/vagrant/libs/django/django/core/management/commands/migrate.py", line 246, in handle
    fake_initial=fake_initial,
  File "/vagrant/libs/django/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/vagrant/libs/django/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/vagrant/libs/django/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/vagrant/libs/django/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/vagrant/libs/django/django/db/migrations/operations/fields.py", line 244, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/vagrant/libs/django/django/db/backends/base/schema.py", line 595, in alter_field
    old_db_params, new_db_params, strict)
  File "/vagrant/libs/django/django/db/backends/base/schema.py", line 756, in _alter_field
    params,
  File "/vagrant/libs/django/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "/vagrant/libs/django/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/vagrant/libs/django/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/vagrant/libs/django/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/vagrant/libs/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/vagrant/libs/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/vagrant/libs/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/vagrant/libs/django/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/home/vagrant/.virtualenvs/wagtailmysql/lib/python3.6/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/vagrant/.virtualenvs/wagtailmysql/lib/python3.6/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/vagrant/.virtualenvs/wagtailmysql/lib/python3.6/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1101, "BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value")

Bisecting shows that this error was introduced in https://github.com/django/django/commit/6b16c91157512587017e9178d066ed1a683e7795 - presumably prior to this the default was simply being ignored on MySQL.

Change History (11)

comment:1 Changed 21 months ago by Mariusz Felisiak

Cc: Adam Johnson added
Keywords: mysql default added
Triage Stage: UnreviewedAccepted

Thanks for the report, I can reproduce this issue. TBH, I'm not sure why it doesn't work. I couldn't find any matching caveat in the MySQL docs , and adding TextField() with a default works properly, e.g.

migrations.AddField(
    model_name='mymodel',
    name='test_field_2',
    field=models.TextField(blank=True, default=''),
)

ALTER TABLE `test_two_mymodel` ADD COLUMN `test_field_2` longtext DEFAULT ('') NOT NULL;

Crashing SQL:

ALTER TABLE `test_one_article` ALTER COLUMN `body` SET DEFAULT ('');

Regression in 6b16c91157512587017e9178d066ed1a683e7795.
Reproduced at f55f3ce831fa885dfef0b222c254bb4bf3ca99ef.

Last edited 21 months ago by Mariusz Felisiak (previous) (diff)

comment:2 Changed 21 months ago by Matt Westcott

Cc: Matt Westcott added

comment:3 Changed 20 months ago by Matt Westcott

For anyone following along, I worked around this by splitting the AlterField operation into two steps - first setting the field as non-nullable, and then changing it to a TextField: https://github.com/wagtail/wagtail/pull/6999/files

The breakage here still seems quite concerning though - given the preserve_default=False, wouldn't we expect that the default value never actually hits the database schema?

comment:4 Changed 19 months ago by Jacob Walls

Has patch: set
Owner: changed from nobody to Yuekui
Status: newassigned

comment:5 Changed 19 months ago by Mariusz Felisiak

I've checked MySQL 8.0.13 release notes once more time:

"MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all. For details, see Data Type Default Values."

it looks that MySQL support is limited to the ADD COLUMN statement. We should revert 6b16c91157512587017e9178d066ed1a683e7795 and close #30712 as wontfix.

comment:6 Changed 19 months ago by Mariusz Felisiak

After reconsideration, let's leave the partial support (see also Simon's comment).

comment:7 Changed 19 months ago by Mariusz Felisiak

Triage Stage: AcceptedReady for checkin

comment:8 Changed 19 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In 5e04e84d:

Fixed #32503 -- Fixed altering BLOB/TEXT field to non-nullable with default on MySQL 8.0.13+.

MySQL 8.0.13+ supports defaults for BLOB/TEXT but not in the
ALTER COLUMN statement.

Regression in 6b16c91157512587017e9178d066ed1a683e7795.

Thanks Matt Westcott for the report.

comment:9 Changed 18 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 57146ba:

[3.2.x] Fixed #32503 -- Fixed altering BLOB/TEXT field to non-nullable with default on MySQL 8.0.13+.

MySQL 8.0.13+ supports defaults for BLOB/TEXT but not in the
ALTER COLUMN statement.

Regression in 6b16c91157512587017e9178d066ed1a683e7795.

Thanks Matt Westcott for the report.

Backport of 5e04e84d67da8163f365e9f5fcd169e2630e2873 from main

comment:10 Changed 18 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 57bc16b3:

Refs #32503 -- Added release notes for 5e04e84d67da8163f365e9f5fcd169e2630e2873.

comment:11 Changed 18 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In bb29174:

[3.2.x] Refs #32503 -- Added release notes for 5e04e84d67da8163f365e9f5fcd169e2630e2873.

Backport of 57bc16b38ec75fc96829f912d57a58d8c6358e8f from main

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