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

[Jan Schneider]

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 options 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)
- ???

Other places that might be affected:
- group names
- tag names (we already normalize them in php code)
- vilma domain and user names
- wiki page names

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/