Opened 2 years ago
Closed 2 years ago
#34219 closed Bug (fixed)
Collation is not preserved when field is altered on PostgreSQL and MySQL.
Reported by: | David Foster | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.2 |
Severity: | Normal | Keywords: | |
Cc: | Tom Carrick, David Wobrock | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Attachments (1)
Change History (5)
comment:1 by , 2 years ago
Cc: | added |
---|---|
Summary: | Custom field collation (db_collation) is not preserved when field is altered → Collation is not preserved when field is altered on PostgreSQL and MySQL. |
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 2 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
I decided to work on this as we will have a similar issue with database comments.
Thanks for the ticket. Would you like to prepare a patch?