[rt-users] upgrade from 3.6 to 4 mysql upgrade error

[Kevin Falcone]

On Wed, Mar 05, 2014 at 01:47:42PM -0700, George Kelbley wrote:
> 
> ERROR 1062 (23000) at line 220: Duplicate entry '' for key 'Users1'
> 
> This problem appears in some other postings on the list, but I'm not
> sure how to proceed.  If its caused by
> a bad ticket, (this database was started in 1997!),  maybe I can
> shred the ticket(s) in question, but version 3.6
> does not support shredding if I'm reading things correctly.

I believe I've answered this before, but as I can't trivially find the
message, I'll recreate it here.

What has happened is that your Users table contains some users whose
Users.Name contain invalid characters and so when they're transformed
into UTF8 there is no valid mapping and they collapse to ''.
Once there are two of those, you validate the unique index on Name.

So, to proceed.
SHOW CREATE TABLE Users; # confirm that Users1 is the Unique index
drop index Users1 on Users;
copy/paste the last ALTER TABLE statement from queries.sql and
rerun it (or create a new file containing only the last statement and
run it).
select * from Users where Name = '';
    marvel at the encoding failures
update Users set Name = CONCAT('SPAMUSER-',id) where Name = '';
    tweak SPAMUSER as you desire
CREATE UNIQUE INDEX Users1 ON Users (Name) ;

Generally, once I know that I have a data set with a failure like
this, I rework my upgrade to do

drop index;
mysql -uroot -p < queries.sql
update;
create index;

I'll look into adding some part of this to UPGRADING.mysql

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