[dev] Case insensitive unique key, was: [horde] Error when updating DB scheme for kronolith

[Chuck Hagenbuch]

Ronan SALMON <rsalmon at mbpgroup.com> wrote:

Chuck Hagenbuch <chuck at horde.org> a écrit : > Quoting Jan Schneider <jan at horde.org>: > >> Zitat von MarkatOSI <buy at mark.net>: >> >>> Problem solved. I found a similar situation described in another posting >>> >>> "I'm having the same issue : >>> SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry >>> 'Nmaurice' for key 'rampage_users_user_name' >>> >>> But over here nmaurice is a user and in our case, I'm guessing that >>> this user usually logs in as 'nmaurice', and logged in at least once >>> as 'Nmaurice'. >>> >>> After running the following query, the migrate script was happy. >>> mysql> update kronolith_events set >>> event_creator_id=lower(event_creator_id); >> >> The core problem is that we have a unique key on >> rampage_users.user_name. Whether this key is case sensitive or not >> depends on the table's/database's collation in MySQL. By default >> any indexes are case insensitive. This breaks because Horde itself >> is case sensitive. >> The optio
 ns that
I see are: >> - normalize usernames in Content (not good because it would make >> Horde case insenstive in a single place) >> - enforce collation during table creation (not portable, not sure >> if possible, you can't just pick cs vs ci but also have to pick a >> locale for the collation) >> - make this a regular key an ensure uniqueness in userland code >> (not as elegant) >> - ??? > > What about making Horde usernames case insensitive everywhere? > > I know that we have made a policy of not mucking with usernames, but > does anyone actually use case sensitivity to make joe and Joe > different accounts? It seems to cause a lot of confusion for people, > as well as posing problems like this one. This could be a Horde configuration option, and by default set to insensitive username. Ronan. 


That still leaves us with the existing problem when it's set to case sensitive ...