RT Upgrade 3.0.6 -> 3.8.5 database problem

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

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@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@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.

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

Torsten Olschewski wrote:

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.

Try this MySQL query:

SELECT COUNT(), id, Name, EmailAddress from Users GROUP BY EmailAddress
HAVING COUNT(
) > 1 order by Name, EmailAddress;

That’ll return a list of all the users with identical email addresses.
You will then have to decide what to do with them - if they have only
spam (or no) tickets, you can shred them without any danger.

Graeme