MySQL related Bugs in RT 3.8 while Upgrading from 3.6

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@cancom.de mailto:torben.nehmer@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!

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:

Thank you. I’ve opened a ticket for this.

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

How long has MySQL done that? I’ve never experienced it. Regardless,
I’ve opened a ticket for this as well.

Thanks again for the report.

Jesse

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:

Thank you. I’ve opened a ticket for this.

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

How long has MySQL done that? I’ve never experienced it. Regardless, I’ve
opened a ticket for this as well.
Heh, I’ve missed that as my mysql uses UTF-8 by default.

Thanks again for the report.
Jesse


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 Jesse,

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

How long has MySQL done that? I’ve never experienced it. Regardless, I’ve opened a ticket for this as well.

I am not sure which MySQL Versions actually show this behavior. I suspect that it has to do with the actual Debian based installation I am using here. Maybe the MySQL System is forced to some charset or doesn’t correctly honor the locale, maybe it is even a bug. I cannot tell for sure, unfortunalety.

Independent of this I think it is better to be safe than sorry, as the saying goes.

Thanks again for the report.

No problem, please tell me if you need additional information and / or Tests regarding this problem. My RT 3.8 installation is still in testing, so doing all kinds of experimental Stuff is no problem. I’m waiting for 3.8.1 to go productive here :wink:

Greetings from Germany,
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@cancom.de mailto:torben.nehmer@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!Von: Jesse Vincent [mailto:jesse@bestpractical.com]
Gesendet: Sonntag, 27. Juli 2008 02:15
An: Torben Nehmer
Cc: rt-users@lists.bestpractical.com
Betreff: Re: [rt-users] MySQL related Bugs in RT 3.8 while Upgrading from 3.6

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:

Thank you. I’ve opened a ticket for this.

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

How long has MySQL done that? I’ve never experienced it. Regardless, I’ve opened a ticket for this as well.

Thanks again for the report.

Jesse