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

[Kevin Falcone]

On Tue, Jan 26, 2010 at 04:53:58PM +0000, 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...

Temporarily drop the unique constraint on Name and rerun the
conversion, then look for duplicated usernames / usernames that are
blank.  That'll give you an id that you can use to clean up the name
in the original content before you do your final cutover.

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

As long as you don't violate the unique constraint it is fine to
change the Name

-kevin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 195 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100126/45b8a1b0/attachment.sig>