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

[Ruslan Zakirov]

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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: RT-3.8.0-mysql_utf8_connection.patch
Type: application/octet-stream
Size: 963 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080728/abf70ad1/attachment.obj>