[rt-users] MySQL related Bugs in RT 3.8 while Upgrading from 3.6

[Torben Nehmer]

Hi there,

 

I have been testing the upgrade of our (originally Debian Based) RT 3.6.5 installation to RT 3.8 and found two bugs in the process:

 

First, the script generating the necessary code to convert the Database from MySQL 4.0 to 4.1 and newer produces corrupt SQL at least in my case here. It has several occurrences of constructs like this:

 

                ALTER TABLE Groups MODIFIY Domain VARBINARY(64) NOT NULL DEFAULT NULL;

 

Note, that the NOT NULL DEFAULT NULL contradicts itself, MySQL 5.0.51a (Debian) at least rejects it as a syntax error. When changing all of the above occurrences in the database to "NULL DEFAULT NULL" everything works. This seems as the best alternative to me, please correct me if I am wrong. "NOT NULL" in itself will produce errors, changing the default away from NULL might have consequences in RT itself.

 

I do not have a patch here, as I have fixed the sql.queries script directly with vi.

 

The second problem I have tested so far only with the RT Standalone server, I cannot say anything (yet) for other ways to run RT as I'm still in the process of testing everything.

 

At least Debian MySQL 5.0.51a does by default initialize all connections in latin1 mode. So after converting the Database to correct UTF-8, MySQL does automatically convert any columns known as UTF-8 into the default connection charset latin1. This leads to all broken non-ASCII Chars all over the site except the Attachments (where RT itself appearantly does the conversion handling, as this is a BINARY field).

 

I was unable to change MySQLs default behavior using my.cnf - this isn't sensible anyway, as this could have side effects with other applications that do make any implicit assumption about the connection character set.

 

After some testing I found an easy way (for MySQL) to actually enforce the usage of UTF-8 as connection charset inside RT by patching the connection startup in Handle.pm like this:

 

--- /home/nehmer/src/rt-3.8.0/lib/RT/Handle.pm  2008-06-14 00:06:41.000000000 +0200

+++ Handle.pm   2008-07-24 09:33:14.208864208 +0200

@@ -113,6 +113,11 @@

         );

 

     $self->dbh->{'LongReadLen'} = RT->Config->Get('MaxAttachmentSize');

+

+    if ( RT->Config->Get('DatabaseType') eq 'mysql' ) {

+        $self->dbh->do("set character set utf8");

+        $self->dbh->do("set names utf8");

+    }

 }

 

 =head2 BuildDSN

 

Here again I am not sure if this is the best solution (I would have assumed that DBI does this automagically when Perl's in UTF-8 mode), but it most certainly does work. So far I could not find another working solution. Any default character set I define in my.cnf seems to have no effect at this point, as well as the Locale I use when starting up the Standalone server (I was testing it with both POSIX and de_DE.UTF8.

 

 

I would appreciate some feedback on these two patches, whether they are ok or if they are additional points I might have missed. Especially, since so far I have only had time for rudimentary testing.

 

 

Apart from that I'd like to say: Good Work! RT 3.8 looks really promising. Can't wait for 3.8.1, which we'll most probably take into production when it comes out. 

 

Yours,
Torben Nehmer

-------
Torben Nehmer
Diplom Informatiker (FH)
Business System Developer

CANCOM Deutschland GmbH
Messerschmittstr. 20
89343 Scheppach
Germany

Tel.: +49 8225 - 996-1118
Fax: +49 8225 - 996-41118
torben.nehmer at cancom.de <mailto:torben.nehmer at cancom.de> 
www.cancom.de <http://www.cancom.de> 

CANCOM Deutschland GmbH
Sitz der Gesellschaft: Jettingen-Scheppach
HRB 10653 Memmingen
Geschäftsführer: Paul Holdschik, Christian Linder

Diese E-Mail und alle mitgesendeten Dateien sind vertraulich und ausschließlich für den Gebrauch durch den Empfänger bestimmt! 
This e-mail and any files transmitted with it are confidential intended solely for the use of the addressee!

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080724/290baf89/attachment.htm>