[rt-users] RT Upgrade 3.0.6 -> 3.8.5 database problem

[Torsten Olschewski]

Hi all,

Upgrade of RT software is done without problems following the README
file.

But upgrading the MySQL Database makes one problem with importing the
SQL file generated by upgrade-mysql-schema.pl at point 6 in
UPGRADING.mysql. If I import the SQL file with

mysql -u root -p rt3 < sql.queries

the command stops with the following error:

ERROR 1062 (23000) at line 220: Duplicate entry 'Санкт-Петербург' for key 2

Line 220 and following in sql.queries:
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 '';

I'm not a MySQL expert. Does the script break at the first error and
corrupted the database? Or do i have "only" some data with wrong
character set?

After that i can upgrade the database with rt-setup-database to
RT version 3.8.5 and login normaly without problems.

Google has led me to this:

http://www.mail-archive.com/rt-users@lists.bestpractical.com/msg17534.html

Maybe I can play with MySQL collation but how can I find users with
wrong names?


old system:
SLES9 32bit
RT 3.0.6
Perl 5.8.3
MySQL 5.0.51a
Apache 1.3
ModPerl1

new system:
SLES10SP2 64bit
RT 3.8.6
Perl 5.8.8
MySQL 5.0.67 x86_64
Apache 2.2
ModPerl2


--
regards

Torsten