Upgrade 3.6.5->3.8.7: Duplicate entry '' for key 2

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

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

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:

Kevin Falcone wrote:

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.

Thanks Kevin, that did the trick nicely. Now I just need to find a
convenient hour to do the real upgrade…

Cheers,

Howie