[Django] #34219: Custom field collation (db_collation) is not preserved when field is altered

[Django]

#34219: Custom field collation (db_collation) is not preserved when field is
altered
-------------------------------------+-------------------------------------
Reporter: David | Owner: nobody
Foster |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
At least for MySQL databases, if you make a change to a model field that
has a custom db_collation, the resulting migration will run an ALTER TABLE
statement that reverts the collation of the associated column to the
enclosing table's default collation, rather than preserving the custom
db_collation defined on the field.

For example, here is a model with a field whose db_collation is
customized:

{{{
class TeacherUploadedVideo(models.Model):
filename = models.CharField(
max_length=100,
db_collation='utf8mb4_0900_as_cs', # custom collation
)
}}}

Django will initially create a table for that model which looks like:

{{{
mysql> SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME =
'planner_teacheruploadedvideo' ORDER BY COLUMN_NAME;
+---------------------------------+--------------------+
| COLUMN_NAME | COLLATION_NAME |
+---------------------------------+--------------------+
| filename | utf8mb4_0900_as_cs |
| id | NULL |
+---------------------------------+--------------------+
}}}

But if you then alter the field slightly, such as by altering the
max_length from 100 to 120, Django will generate a migration that does NOT
preserve the customized db_collation.

{{{
$ cat planner/migrations/0086_alter_teacheruploadedvideo_filename.py
...
class Migration(migrations.Migration):
...
operations = [
migrations.AlterField(
model_name='teacheruploadedvideo',
name='filename',
field=models.CharField(db_collation='utf8mb4_0900_as_cs',
max_length=120),
),
]
}}}

{{{
$ python3 manage.py sqlmigrate planner 0086
ALTER TABLE `planner_teacheruploadedvideo` MODIFY `filename` varchar(120)
NOT NULL;
}}}

In particular the generated ALTER TABLE SQL statement is lacking the
COLLATE clause that would preserve the field's collation. By omitting the
COLLATE clause, MySQL will revert the column's collation to its table's
default collation during the ALTER. A corrected SQL statement would be:

{{{
ALTER TABLE `planner_teacheruploadedvideo` MODIFY `filename` varchar(120)
COLLATE utf8mb4_0900_as_cs NOT NULL;
}}}

It looks like this issue may also affect Postgres databases, since the
[https://www.postgresql.org/docs/current/sql-altertable.html ALTER TABLE
syntax for Postgres] also includes a COLLATE clause, although I have not
checked myself.

--
Ticket URL: <https://code.djangoproject.com/ticket/34219>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.