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)

test-34219.diff (1.9 KB ) - added by Mariusz Felisiak 2 years ago.
Regression test.

Download all attachments as: .zip

Change History (5)

comment:1 by Mariusz Felisiak, 2 years ago

Cc: Tom Carrick David Wobrock added
Summary: Custom field collation (db_collation) is not preserved when field is alteredCollation is not preserved when field is altered on PostgreSQL and MySQL.
Triage Stage: UnreviewedAccepted

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

by Mariusz Felisiak, 2 years ago

Attachment: test-34219.diff added

Regression test.

comment:2 by Mariusz Felisiak, 2 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

I decided to work on this as we will have a similar issue with database comments.

Version 0, edited 2 years ago by Mariusz Felisiak (next)

comment:3 by Mariusz Felisiak, 2 years ago

Has patch: set

comment:4 by GitHub <noreply@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

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).

Note: See TracTickets for help on using tickets.
Back to Top