Collation is not preserved when field is altered on PostgreSQL and MySQL.

[David Foster]

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 ​ALTER TABLE syntax for Postgres also includes a COLLATE clause, although I have not checked myself.

[Mariusz Felisiak]

Thanks for the ticket. Would you like to prepare a patch?

[Mariusz Felisiak]

Regression test.

[Mariusz Felisiak]

I decided to work on this as we will have a similar issue with database comments (see #18468).

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In ae0899b:

Fixed #34219 -- Preserved Char/TextField.db_collation when altering column type.

This moves setting a database collation to the column type alteration
as both must be set at the same time.

This should also avoid another layer of the column type alteration when
adding database comments support (#18468).