FYI collisions when object_ids are case sensitive

[borwickatuw]

FYI when using django-reversion along with HashidAutoField from django-hashid-field, I observed that the Hashid values are case-sensitive but the default MySQL column for reversion_version is case-insensitive.

This can show up as receiving version history for more than one object when running

Version.objects.get_for_model(ModelUsingHashidAutoField).filter(object_id=HashedID)

For example, one object might have object_id of aBcDeF and another object has object_id of AbCdEf.

This is a database-level issue because by default MySQL does case-insensitive collation on varchar fields.

To address this issue, I ran:

ALTER TABLE reversion_version MODIFY object_id VARCHAR(191) BINARY NOT NULL;

(HashidAutoField itself doesn't run into this problem because those ids aren't actually stored in the database. They're calculated on the fly.)

I hope this helps others!

[etianen]

Huh. Are you aware of any way to fix this in django-reversion?

On Wed, 20 Oct 2021 at 16:36, borwickatuw @.***> wrote:

FYI when using django-reversion along with HashidAutoField from django-hashid-field https://github.com/nshafer/django-hashid-field, I observed that the Hashid values are case-sensitive but the default MySQL column for reversion_version is case-insensitive.

This can show up as receiving version history for more than one object when running

Version.objects.get_for_model(ModelUsingHashidAutoField).filter(object_id=HashedID)

For example, one object might have object_id of aBcDeF and another object has object_id of AbCdEf.

This is a database-level issue because by default MySQL does case-insensitive collation on varchar fields.

To address this issue, I ran:

ALTER TABLE reversion_version MODIFY object_id VARCHAR(191) BINARY NOT NULL;

(HashidAutoField itself doesn't run into this problem because those ids aren't actually stored in the database. They're calculated on the fly.)

I hope this helps others!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/etianen/django-reversion/issues/896, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABEKCE2ZZEH3BTSEPVWGK3UH3OXDANCNFSM5GL6QZLA . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[borwickatuw]

I'm not sure how many database backends have this issue, or how common case-sensitive object_ids are, which is why I just shared the manual way to do it. WIthout looking into it too closely I see a StackOverflow recommendation to create a custom Django model field https://stackoverflow.com/a/39221772 , but I don't know whether that's a good idea.

Maybe a workaround could be a custom database migration for MySQL to set the field to be case-insensitive? https://stackoverflow.com/a/38304105 That's probably not the best idea either :/

[etianen]

Fair enough. Thanks for reporting this issue. It'll be a good record for anyone else with the same issue.

I'm not in a good position to dev against MySQL currently. If someone who regularly uses MySQL wants to contribute an MR to fix this at source, I'm very happy to accept! :pleading_face: