Mysql upgrading to RT3.8 gives you garbled UTF8 text

First, thanks for the 3.8 upgrade. Much appreciated.

I already had accents in my tickets, and running schema.mysql-4.0-4.1.pl
would garble them. This is using mysql 5.0.51a on Redhat.

I discovered that if I skip the VARBINARY part of the upgrade, the data
is fine. So instead of

ALTER TABLE Tickets MODIFY Subject VARBINARY(10) NULL DEFAULT

NULL;
ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET
utf8 NULL DEFAULT NULL;

Just do:

ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET

utf8 NULL DEFAULT NULL;

So, if your data is garbled post upgrade, restore your backup (you had a
backup, right?), and follow these instructions to run
schema.mysql-4.0-4.1.pl.

You can easily get those UTF8 modify commands like this:

perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | grep

MODIFY.*utf8

Of course, you still need to fix the NOT NULL DEFAULT NULL issue. So,
the full command:

perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
grep MODIFY.*utf8 | \
perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
mysql -urt_user -prt_pass rt

Then, run schema.mysql-4.0-4.1.pl normally, the columns that were fixed
already won’t be refixed by the upgrade script again.

perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
mysql -urt_user -prt_pass rt

-Mathieu

Do you have any customizations of RT code?

Older RT versions were not using any connection adjustments like SET
NAMES or SET CHARACTER SET and most users had latin-1 as default mysql
charset. RT was storing UTF-8 data into latin-1 columns what is
theoretically wrong, but practically works fine. In 3.8 we have to
properly define columns as binary, UTF-8 or ascii as actually they do
contain only this data. MySQL’s doc clearly says that the only way is
to convert field to some binary format (no data change) and then to
required character set. All these is described in comments in the
beginning of the script.

Try attached patch instead of what you’ve described below. I’m going
to look at “NOT NULL DEFAULT NULL” problem.On Mon, Jul 28, 2008 at 8:14 PM, Mathieu Longtin mlongtin@dbsoft.ca wrote:

First, thanks for the 3.8 upgrade. Much appreciated.

I already had accents in my tickets, and running schema.mysql-4.0-4.1.pl
would garble them. This is using mysql 5.0.51a on Redhat.

I discovered that if I skip the VARBINARY part of the upgrade, the data
is fine. So instead of

   ALTER TABLE Tickets MODIFY Subject VARBINARY(10) NULL DEFAULT

NULL;
ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET
utf8 NULL DEFAULT NULL;

Just do:

   ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET

utf8 NULL DEFAULT NULL;

So, if your data is garbled post upgrade, restore your backup (you had a
backup, right?), and follow these instructions to run
schema.mysql-4.0-4.1.pl.

You can easily get those UTF8 modify commands like this:

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | grep

MODIFY.*utf8

Of course, you still need to fix the NOT NULL DEFAULT NULL issue. So,
the full command:

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
   grep MODIFY.*utf8 | \
   perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
   mysql -urt_user -prt_pass rt

Then, run schema.mysql-4.0-4.1.pl normally, the columns that were fixed
already won’t be refixed by the upgrade script again.

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
   perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
   mysql -urt_user -prt_pass rt

-Mathieu


The rt-users Archives

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.

RT-3.8.0-mysql_utf8_connection.patch (963 Bytes)

So your patch for utf8 worked, except it’s ‘utf8’, not ‘UTF-8’.

Here’s one for the NOT NULL DEFAULT NULL issue:

diff --git a/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl b/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-
index 491c904…3585c7e 100755
— a/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl
+++ b/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl
@@ -339,11 +339,10 @@ sub build_column_definition {
$res .= ‘NULL’;
my $default = column_info(@info{qw(TABLE_NAME COLUMN_NAME)})->{default};
if ( defined $default ) {

  •    $default = $dbh->quote($default);
    
  • } else {
  •    $default = 'NULL';
    
  •   $res .= ' DEFAULT '.$dbh->quote($default);
    
  • } elsif ( $info{‘NULLABLE’} ) {
  •   $res .= ' DEFAULT NULL'
    
    }

Do you have any customizations of RT code?

Older RT versions were not using any connection adjustments like SET NAMES or SET CHARACTER SET and most users had latin-1 as default mysql charset. RT was storing UTF-8 data into latin-1 columns what is theoretically wrong, but practically works fine. In 3.8 we have to properly define columns as binary, UTF-8 or ascii as actually they do contain only this data. MySQL’s doc clearly says that the only way is to convert field to some binary format (no data change) and then to required character set. All these is described in comments in the beginning of the script.

Try attached patch instead of what you’ve described below. I’m going to look at “NOT NULL DEFAULT NULL” problem.

thanks. applied.On Mon, Jul 28, 2008 at 11:41 PM, Mathieu Longtin mlongtin@dbsoft.ca wrote:

So your patch for utf8 worked, except it’s ‘utf8’, not ‘UTF-8’.

Here’s one for the NOT NULL DEFAULT NULL issue:

diff --git a/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl b/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-
index 491c904…3585c7e 100755
— a/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl
+++ b/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl
@@ -339,11 +339,10 @@ sub build_column_definition {
$res .= ‘NULL’;
my $default = column_info(@info{qw(TABLE_NAME COLUMN_NAME)})->{default};
if ( defined $default ) {

  •    $default = $dbh->quote($default);
    
  • } else {
  •    $default = 'NULL';
    
  •   $res .= ' DEFAULT '.$dbh->quote($default);
    
  • } elsif ( $info{‘NULLABLE’} ) {
  •   $res .= ' DEFAULT NULL'
    
    }
  • $res .= ’ DEFAULT ‘. $default;
    $res .= ’ AUTO_INCREMENT’ if $info{‘mysql_is_auto_increment’};
    return $res;
    }

-----Original Message-----
From: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] On Behalf Of Ruslan Zakirov
Sent: July 28, 2008 14:41
To: Mathieu Longtin
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Mysql upgrading to RT3.8 gives you garbled UTF8 text

Do you have any customizations of RT code?

Older RT versions were not using any connection adjustments like SET NAMES or SET CHARACTER SET and most users had latin-1 as default mysql charset. RT was storing UTF-8 data into latin-1 columns what is theoretically wrong, but practically works fine. In 3.8 we have to properly define columns as binary, UTF-8 or ascii as actually they do contain only this data. MySQL’s doc clearly says that the only way is to convert field to some binary format (no data change) and then to required character set. All these is described in comments in the beginning of the script.

Try attached patch instead of what you’ve described below. I’m going to look at “NOT NULL DEFAULT NULL” problem.

On Mon, Jul 28, 2008 at 8:14 PM, Mathieu Longtin mlongtin@dbsoft.ca wrote:

First, thanks for the 3.8 upgrade. Much appreciated.

I already had accents in my tickets, and running
schema.mysql-4.0-4.1.pl would garble them. This is using mysql 5.0.51a on Redhat.

I discovered that if I skip the VARBINARY part of the upgrade, the
data is fine. So instead of

   ALTER TABLE Tickets MODIFY Subject VARBINARY(10) NULL DEFAULT

NULL;
ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET
utf8 NULL DEFAULT NULL;

Just do:

   ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET

utf8 NULL DEFAULT NULL;

So, if your data is garbled post upgrade, restore your backup (you had
a backup, right?), and follow these instructions to run
schema.mysql-4.0-4.1.pl.

You can easily get those UTF8 modify commands like this:

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | grep

MODIFY.*utf8

Of course, you still need to fix the NOT NULL DEFAULT NULL issue. So,
the full command:

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
   grep MODIFY.*utf8 | \
   perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
   mysql -urt_user -prt_pass rt

Then, run schema.mysql-4.0-4.1.pl normally, the columns that were
fixed already won’t be refixed by the upgrade script again.

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
   perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
   mysql -urt_user -prt_pass rt

-Mathieu


The rt-users Archives

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.


The rt-users Archives

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.

Hmm…

I was just about to start an upgrade till RT 3.8.0 when I noticed these
messages regarding these issues. Is there a complete list of what needs
to be fixed to get it to work, or should one just wait for RT 3.8.1 to
pop up?

  • Peter

Mathieu Longtin wrote:

signature.asc (185 Bytes)

You can help test upgrade by using copy of your DB and RT from our
repository. That would be really helpful.On Tue, Jul 29, 2008 at 12:59 PM, Peter Eriksson peter@ifm.liu.se wrote:

Hmm…

I was just about to start an upgrade till RT 3.8.0 when I noticed these
messages regarding these issues. Is there a complete list of what needs
to be fixed to get it to work, or should one just wait for RT 3.8.1 to
pop up?

  • Peter

Mathieu Longtin wrote:

So your patch for utf8 worked, except it’s ‘utf8’, not ‘UTF-8’.

Here’s one for the NOT NULL DEFAULT NULL issue:

diff --git a/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl b/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-
index 491c904…3585c7e 100755
— a/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl
+++ b/tmp/rt-3.8.0/etc/upgrade/schema.mysql-4.0-4.1.pl
@@ -339,11 +339,10 @@ sub build_column_definition {
$res .= ‘NULL’;
my $default = column_info(@info{qw(TABLE_NAME COLUMN_NAME)})->{default};
if ( defined $default ) {

  •    $default = $dbh->quote($default);
    
  • } else {
  •    $default = 'NULL';
    
  •   $res .= ' DEFAULT '.$dbh->quote($default);
    
  • } elsif ( $info{‘NULLABLE’} ) {
  •   $res .= ' DEFAULT NULL'
    
    }
  • $res .= ’ DEFAULT ‘. $default;
    $res .= ’ AUTO_INCREMENT’ if $info{‘mysql_is_auto_increment’};
    return $res;
    }

-----Original Message-----
From: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] On Behalf Of Ruslan Zakirov
Sent: July 28, 2008 14:41
To: Mathieu Longtin
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Mysql upgrading to RT3.8 gives you garbled UTF8 text

Do you have any customizations of RT code?

Older RT versions were not using any connection adjustments like SET NAMES or SET CHARACTER SET and most users had latin-1 as default mysql charset. RT was storing UTF-8 data into latin-1 columns what is theoretically wrong, but practically works fine. In 3.8 we have to properly define columns as binary, UTF-8 or ascii as actually they do contain only this data. MySQL’s doc clearly says that the only way is to convert field to some binary format (no data change) and then to required character set. All these is described in comments in the beginning of the script.

Try attached patch instead of what you’ve described below. I’m going to look at “NOT NULL DEFAULT NULL” problem.

On Mon, Jul 28, 2008 at 8:14 PM, Mathieu Longtin mlongtin@dbsoft.ca wrote:

First, thanks for the 3.8 upgrade. Much appreciated.

I already had accents in my tickets, and running
schema.mysql-4.0-4.1.pl would garble them. This is using mysql 5.0.51a on Redhat.

I discovered that if I skip the VARBINARY part of the upgrade, the
data is fine. So instead of

   ALTER TABLE Tickets MODIFY Subject VARBINARY(10) NULL DEFAULT

NULL;
ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET
utf8 NULL DEFAULT NULL;

Just do:

   ALTER TABLE Tickets MODIFY Subject VARCHAR(10) CHARACTER SET

utf8 NULL DEFAULT NULL;

So, if your data is garbled post upgrade, restore your backup (you had
a backup, right?), and follow these instructions to run
schema.mysql-4.0-4.1.pl.

You can easily get those UTF8 modify commands like this:

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | grep

MODIFY.*utf8

Of course, you still need to fix the NOT NULL DEFAULT NULL issue. So,
the full command:

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
   grep MODIFY.*utf8 | \
   perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
   mysql -urt_user -prt_pass rt

Then, run schema.mysql-4.0-4.1.pl normally, the columns that were
fixed already won’t be refixed by the upgrade script again.

   perl schema.mysql-4.0-4.1.pl rt rt_user rt_pass | \
   perl -pe 's/NOT NULL DEFAULT NULL/NOT NULL/' | \
   mysql -urt_user -prt_pass rt

-Mathieu


The rt-users Archives

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.


The rt-users Archives

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.

I’m having trouble with UTF8 text in 3.8.0 too.

Ruslans patched fixed the special chars in queue names and ticket subjects.
However when viewing tickets, UTF8 text in the email text is still wrong.
But if i click reply/comment on the post, text is shown correctly in the
editor (i’m using the WYSIWYG FCKeditor)

Any idea of what i could patch ?

Ruslan Zakirov wrote: