[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





___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sa...@bestpractical.com


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com

Re: [rt-users] RT Upgrade 3.0.6 - 3.8.5 database problem

[Torsten Olschewski]

Hi Ruslan,

 This happens when Name in the Users table contains duplicate values in
 terms of case-sensetivity or incorrect UTF-8 data that gets converted
 to empty string.
 
 You either have two users with name 'Санкт-Петербург'
 spelled in
 different case or this value is not UTF-8, but cp1251, koi8-r or may
 be cp866. The latter may happen because of spam.

I'am not a MySQL guru. How can I find this users or how can I fix this
problem? There are about 30k users. Most of them from spam mail.

Can I use the option --with-charset=binary with mysqldump to
export the old database? 


Thanks and regards
Torsten

-- 
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com
Commercial support: sa...@bestpractical.com


Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
Buy a copy at http://rtbook.bestpractical.com