Change field index to be case sensitive

Using Django
1

I have tried using django-shortuuidfield on a model, however when searched for the results are case insensitive, ie “9mgReChsS7kNmyTvRUYKtp” is the same as “9mgReChsS7kNmyTvRUYKtP” (note the last character).

class Member(models.Model):
	id = ShortUUIDField(primary_key=True, db_index=True, max_length=22)
	…

I know I can change the collation in MySQL on the column to utf8_bin to fix this, but how can I do that in Django so that the database is managed with migrations?

Thanks

2

See the Collation settings and db_collation sections of the docs.

3

Firstly, thanks to Ken for the pointers! I was looking for “index” so couldn’t find the reference.

Secondly, it is beyond me why the Django documentation cannot link to db_collation from Collation or give an example:

thecol = models.CharField(… db_collation='utf8_bin'…)

Finally, this particular implementation was a bit tricky…

I kept bumping into foreign key constraint errors when running migrate. After some further research I ended up completing the collation change manually through MySQL:

SET foreign_key_checks = 0;
ALTER TABLE b_manage_register MODIFY id VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE b_manage_member_register MODIFY id VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE b_manage_member_register MODIFY member_id VARCHAR(22) CHARACTER SET utf8 COLLATE utf8_bin;
…
SET foreign_key_checks = 1;

And check the tables with:

SHOW FULL COLUMNS FROM b_manage_register;

Then I ran migrate --fake.

I hope that’s the way to do it.

4

After trying to work with this for a while I think there is a small invertebrate in the Django code.

When trying to build/update databases by generating migrations I found that migrate would error with references to foreign key contraints. I could not really figure it out, so ran sqlmigrate on the migration file. That exposed the error:

-- Alter field id on account
--
ALTER TABLE `b_manage_account` MODIFY `id` varchar(22) COLLATE `utf8_bin`;
ALTER TABLE `b_manage_profile` MODIFY `account_id` varchar(22) NULL;
ALTER TABLE `b_manage_member` MODIFY `account_id` varchar(22) NOT NULL;
ALTER TABLE `b_manage_address` MODIFY `account_id` varchar(22) NOT NULL;
ALTER TABLE `b_manage_profile` ADD CONSTRAINT …

The first four statements run without error, then there follow three ADD CONSTRAINT statements that all cause an error. The crux of the problem is that while Django recognises that when b_manage_account.id is modified three statements have to follow that modify where that field is used as a fk in other tables. The trouble is that each of those subsequent changes also need to change the collation on those fk fields, but do not.

So, the correct version of this statement should be:

-- Alter field id on account
--
ALTER TABLE `b_manage_account` MODIFY `id` varchar(22) COLLATE `utf8_bin`;
ALTER TABLE `b_manage_profile` MODIFY `account_id` varchar(22) NULL COLLATE `utf8_bin`;
ALTER TABLE `b_manage_member` MODIFY `account_id` varchar(22) NOT NULL COLLATE `utf8_bin`;
ALTER TABLE `b_manage_address` MODIFY `account_id` varchar(22) NOT NULL COLLATE `utf8_bin`;
ALTER TABLE `b_manage_profile` ADD CONSTRAINT …

If I run that as a query in MySQL then the ADD CONSTRAINT statements run as expected.

I checked the tickets database for references to db_collation, and the only one I could find was that requesting this as a new feature. So I think I have found a genuine flaw in the code of 3.2?