Upgrade from 3.6 to 4 mysql upgrade error

RT Users
1

I’m migrating an old 3.6 install of rt to a new server running version 4
(installed on debian wheezy from
packages), using mysql on both old and new servers.

I’ve followed the steps in the documentation for a trial run:

dumped the rt database on my old server with the
–default-character-set=binary option,

and created a new database on the new server with the latin1 character
set, imported the database and
run the upgrade-mysql-schema script. When importing the produced
queries.sql into my database, I get

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.

The ALTER TABLES Users section of the queries.sql has these lines:

ALTER TABLE Users
MODIFY WebEncoding VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY AuthSystem VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY MobilePhone VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY WorkPhone VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY PagerPhone VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY ExternalContactInfoId VARCHAR(100) CHARACTER SET utf8 NULL
DEFAULT NULL,
MODIFY ContactInfoSystem VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT
NULL,
MODIFY HomePhone VARCHAR(30) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Address1 VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY ExternalAuthId VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Comments TEXT CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY NickName VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Address2 VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Timezone VARCHAR(50) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY FreeformContactInfo TEXT CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY RealName VARCHAR(120) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY City VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY EmailAddress VARCHAR(120) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY EmailEncoding VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY State VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Signature TEXT CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Zip VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Organization VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Lang VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Gecos VARCHAR(16) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Country VARCHAR(50) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Name VARCHAR(200) CHARACTER SET utf8 NOT NULL DEFAULT ‘’;

Any thoughts greatly appreciated

2

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

3

Thanks, this seems to have worked. We use rt daily to handle user
problems/requests, but we’ve let it get a little long in the tooth.

At this point I’m trying to figure out how long its going to take for us
to upgrade.

thanks again!On 3/6/14 9:39 AM, Kevin Falcone wrote:

CREATE UNIQUE INDEX Users1 ON Users (Name) ;