Opened 3 years ago

Closed 3 years ago

Last modified 4 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 (12)

comment:1 by Mariusz Felisiak, 3 years ago

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 3 years ago by Mariusz Felisiak (previous) (diff)

comment:2 by Matt Westcott, 3 years ago

Cc: Matt Westcott added

comment:3 by Matt Westcott, 3 years ago

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 by Jacob Walls, 3 years ago

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

comment:5 by Mariusz Felisiak, 3 years ago

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 by Mariusz Felisiak, 3 years ago

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

comment:7 by Mariusz Felisiak, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 57bc16b3:

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

comment:11 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In bb29174:

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

Backport of 57bc16b38ec75fc96829f912d57a58d8c6358e8f from main

comment:12 by Mariusz Felisiak <felisiak.mariusz@…>, 4 months ago

In 4b7fe14:

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

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