[rt-users] Mysql upgrading to RT3.8 gives you garbled UTF8 text

[Ruslan Zakirov]

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 at 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 at gmail.com [mailto:ruslan.zakirov at gmail.com] On Behalf Of Ruslan Zakirov
>> Sent: July 28, 2008 14:41
>> To: Mathieu Longtin
>> Cc: rt-users at 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 at 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
>>> _______________________________________________
>>> 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.
>> _______________________________________________
>> 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.