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
pgpoh3ckkPGSS.pgp
Description: PGP signature
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] 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
