[rt-users] Upgrade 3.6.5->3.8.7: Duplicate entry '' for key 2

Curtis Bruneau curtisb at vianet.ca
Tue Jan 26 15:50:45 EST 2010


This is due to the UTF8 collation recognizing accents as being the same. 
The old latin collation saw them as unique. There's no real fix other 
then going utf8_binary but that would make everything case sensitive. 
Basically latin1 in mysql is "CI_AS" and utf8 is "CI_AI". For me it was 
the email field that had a unique index preventing it, emails aren't 
suppose to have accents  so you could convert them to the bare character.

Curtis

Howard Jones wrote:
> I've been doing a test migration from RT 3.6.5 to 3.8.7 on a spare VM, 
> prior to doing it on our live server. I noticed there were a lot of db 
> changes, and wanted to be sure that it would all be smooth, and that I 
> knew how long it would be down. Good thing I did, because while running 
> the schema update script produced by upgrade-mysql-schema.pl, I get:
>
> ERROR 1062 (23000): Duplicate entry '' for key 2
>
> After breaking all the ALTER statements into single changes, the 
> offending one is the very last one:
>    ALTER TABLE Users MODIFY Name VARCHAR(200) CHARACTER SET utf8 NOT 
> NULL DEFAULT '';
>
> I have only one user with a name of ''. I have changed that to 
> 'randomstring', and I still get this error. As I understand it, it's to 
> do with how mysql transliterates from one character set to another. So 
> how can I figure out which of my 35000 usernames will transliterate to 
> an empty string? No doubt the users in question are spammers, but I 
> still need to find them to fix them...
>
> Also, am I right to assume that it's OK to just change the Name field? 
> It's the ID that is used elsewhere, right?
>
> Thanks,
>
> Howie
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
> 2010 RT Training Sessions!
> San Francisco, CA, USA - Feb 22 & 23
> Dublin, Ireland - Mar 15 & 16
> Boston, MA, USA - April 5 & 6
> Washington DC, USA - Oct 25 & 26
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
>
>
>   




More information about the rt-users mailing list