AlterField migration to TextField with default fails on MySQL 8.0.13+

[Matt Westcott]

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.

[Mariusz Felisiak]

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.

[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?

[Jacob Walls]

​PR

[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.

[Mariusz Felisiak]

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

[Mariusz Felisiak <felisiak.mariusz@…>]

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.

[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

[Mariusz Felisiak <felisiak.mariusz@…>]

In 57bc16b3:

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

[Mariusz Felisiak <felisiak.mariusz@…>]

In bb29174:

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

Backport of 57bc16b38ec75fc96829f912d57a58d8c6358e8f from main

[Mariusz Felisiak <felisiak.mariusz@…>]

In 4b7fe14:

Refs #32503 -- Added assertion for effective default value when altering TextField to non-nullable with default.