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

[Ruslan Zakirov]

Hello Torsten,

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.

On Mon, Sep 28, 2009 at 5:24 PM, Torsten Olschewski
<torsten04 at nurfuerspam.de> wrote:
> 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: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com



-- 
Best regards, Ruslan.