- One of our clients had a large production issue due to a long-standing bug in Rails with case sensitivity.
Here’s the situation: Rails validates_uniqueness_of has a flag called :case_sensitive. This flag defaults to ‘true’, but can be flipped.
MySQL’s default collation is case-insensitive. As a result, queries will, in general, ignore case unless specifically overridden.
So one might imagine that setting :case_sensitive to false would be completely harmless in a standard MySQL application.
One would be wrong. Setting case_sensitive to false changes the query to lowercase the field in question, causing the MySQL database to ignore any indices it may have and turning the validates_uniqueness_of operation from something cheap and quick to something requiring a full table scan.
The open Lighthouse ticket on this issue is: