[Django] #32503: AlterField migration to TextField with default fails on MySQL 8.0.13+
Django
-----------------------------------------+------------------------
Reporter: Matt Westcott | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.0
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 |
-----------------------------------------+------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32503>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Matt Westcott | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.0
Keywords: mysql default | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* cc: Adam Johnson (added)
* keywords: => mysql default
* stage: Unreviewed => Accepted
Comment:
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
[https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:1>
Django
Reporter: Matt Westcott | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.0
Keywords: mysql default | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Matt Westcott):
* cc: Matt Westcott (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:2>
Django
Reporter: Matt Westcott | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.0
Keywords: mysql default | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (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?
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:3>
Django
Reporter: Matt Westcott | Owner: yuekui
Type: Bug | Status: assigned
Component: Migrations | Version: 3.0
Severity: Normal | Resolution:
Keywords: mysql default | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Jacob Walls):
* owner: nobody => yuekui
* status: new => assigned
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/14421 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:4>
Django
Reporter: Matt Westcott | Owner: yuekui
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: mysql default | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:5>
Django
Reporter: Matt Westcott | Owner: yuekui
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: mysql default | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak):
After reconsideration, let's leave the partial support (see also Simon's
[https://github.com/django/django/pull/14421#issuecomment-845414519
comment]).
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:6>
Django
Reporter: Matt Westcott | Owner: yuekui
Type: Bug | Status: assigned
Severity: Normal | Resolution:
Keywords: mysql default | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:7>
Django
Reporter: Matt Westcott | Owner: Yuekui
Type: Bug | Status: closed
Component: Migrations | Version: 3.0
Severity: Normal | Resolution: fixed
Keywords: mysql default | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"5e04e84d67da8163f365e9f5fcd169e2630e2873" 5e04e84d]:
{{{
#!CommitTicketReference repository=""
revision="5e04e84d67da8163f365e9f5fcd169e2630e2873"
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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:8>
Django
Reporter: Matt Westcott | Owner: Yuekui
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: mysql default | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"57146ba388b81aa9f7f47ad1dbca060871c9ca62" 57146ba]:
{{{
#!CommitTicketReference repository=""
revision="57146ba388b81aa9f7f47ad1dbca060871c9ca62"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:9>
Django
Reporter: Matt Westcott | Owner: Yuekui
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: mysql default | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"57bc16b38ec75fc96829f912d57a58d8c6358e8f" 57bc16b3]:
{{{
#!CommitTicketReference repository=""
revision="57bc16b38ec75fc96829f912d57a58d8c6358e8f"
Refs #32503 -- Added release notes for
5e04e84d67da8163f365e9f5fcd169e2630e2873.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:10>
Django
Reporter: Matt Westcott | Owner: Yuekui
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: mysql default | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"bb29174f942dd60da4ccfcf38fb423fb21ed159a" bb29174]:
{{{
#!CommitTicketReference repository=""
revision="bb29174f942dd60da4ccfcf38fb423fb21ed159a"
[3.2.x] Refs #32503 -- Added release notes for
5e04e84d67da8163f365e9f5fcd169e2630e2873.
Backport of 57bc16b38ec75fc96829f912d57a58d8c6358e8f from main
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:11>
Django
Reporter: Matt Westcott | Owner: Yuekui
Type: Bug | Status: closed
Severity: Normal | Resolution: fixed
Keywords: mysql default | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"4b7fe146ccf6c3fa041f3b43099c93a68e78be96" 4b7fe14]:
{{{
#!CommitTicketReference repository=""
revision="4b7fe146ccf6c3fa041f3b43099c93a68e78be96"
Refs #32503 -- Added assertion for effective default value when altering
TextField to non-nullable with default.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32503#comment:12>