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

[Curtis Bruneau]

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
>
>
>