id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
34219	Collation is not preserved when field is altered on PostgreSQL and MySQL.	David Foster	Mariusz Felisiak	"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."	Bug	closed	Database layer (models, ORM)	3.2	Normal	fixed		Tom Carrick David Wobrock	Accepted	1	0	0	0	0	0