Binary attachment corruption?

Seeing corruptions for any and all binary attachments. rt-3.4.5, mysql
4.1.19. I am suspecting that this is a mysql problem.

In apache2.conf

AddDefaultCharset UTF-8

PerlModule Apache::DBI

#PerlFreshRestart On

#PerlRequire /home/httpd/html/support.serreyn.com/rt-3.4.3/bin/webmux.pl

PerlRequire /home/httpd/html/support.serreyn.com/rt-3.4.5/bin/webmux.pl

AddDefaultCharset UTF-8

In my.cnf

[mysql]

character-sets-dir=/usr/share/mysql/charsets

default-character-set=utf8

[mysqld]

character-set-server = utf8

default-character-set = utf8

mysqladmin version

mysqladmin Ver 8.41 Distrib 4.1.19, for pc-linux-gnu on i686

Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.19

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/run/mysqld/mysqld.sock

Uptime: 8 days 16 hours 30 min 29 sec

Threads: 2 Questions: 77797 Slow queries: 0 Opens: 223 Flush tables: 1
Open tables: 64 Queries per second avg: 0.104

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/

Seeing corruptions for any and all binary attachments. rt-3.4.5, mysql
4.1.19. I am suspecting that this is a mysql problem.

Did you dump and reload without specifying
–with-default-charset=binary? Mysql 4.1 changed their default behaviour
in such a way that it can corrupt RT’s attachments.

We’re working on changes for 3.6.1 to better beat mysql into submission

In my case it is an existing rt installation, that has started to experience
this problem I believe since the mysql upgrade. New attachments sent in are
always corrupted (ok only the binary ones).

Ted

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/From: Mike Patterson [mailto:mikep@berkeley.edu]
Sent: Monday, May 29, 2006 12:05 AM
To: Ted Serreyn
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] binary attachment corruption?

Ted,

I had a binary attachment problem when I attempted to restore a backed
up mysql rt database.
I wasn’t backing up in a way that kept the binary attachments intact.

See subject “binary attachments corrupt in migration to new box”
5/11/2006 in the archive
I had to add this flag “–default-character-set=binary” to my dump
command before I could get a good migration to another server.

mysqldump --opt --add-drop-table --single-transaction --user=root
–password=my_pass_was_here rt3 | gzip > /usr/backup/rt3_db_last2.sql.gz

Is this part of the problem you’re experiencing?
When I was having this issue, new attachments weren’t corrupt, just the
migrated ones.

Fortunately I caught it in test migrations…

Good luck,
Mike

Ted Serreyn wrote:

Seeing corruptions for any and all binary attachments. rt-3.4.5,
mysql 4.1.19. I am suspecting that this is a mysql problem.

In apache2.conf

AddDefaultCharset UTF-8

PerlModule Apache::DBI

#PerlFreshRestart On

#PerlRequire /home/httpd/html/support.serreyn.com/rt-3.4.3/bin/webmux.pl

PerlRequire /home/httpd/html/support.serreyn.com/rt-3.4.5/bin/webmux.pl

AddDefaultCharset UTF-8

In my.cnf

[mysql]

character-sets-dir=/usr/share/mysql/charsets

default-character-set=utf8

[mysqld]

character-set-server = utf8

default-character-set = utf8

mysqladmin version

mysqladmin Ver 8.41 Distrib 4.1.19, for pc-linux-gnu on i686

Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license

Server version 4.1.19

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/run/mysqld/mysqld.sock

Uptime: 8 days 16 hours 30 min 29 sec

Threads: 2 Questions: 77797 Slow queries: 0 Opens: 223 Flush
tables: 1 Open tables: 64 Queries per second avg: 0.104**


Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/



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

We’re hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html

In my case it is an existing rt installation, that has started to experience
this problem I believe since the mysql upgrade. New attachments sent in are
always corrupted (ok only the binary ones).

I am having the same problem w/4.1.19. Bueller?

Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com

Jesse may have inadvertently touched on this in his response. It looks
like your default character set is utf8, but Jesse suggested doing dumps
with --with-default-charset=binary. Hmmm…2+2= ___ maybe? Anyone
tried setting
[mysqld]
character-set-server = binary
and reporting back?

But I’m still using an old mysql, so I’m not real sure-like.

Covington, Chris wrote:> On Mon, May 29, 2006 at 09:30:48AM -0500, Ted Serreyn wrote:

In my case it is an existing rt installation, that has started to experience
this problem I believe since the mysql upgrade. New attachments sent in are
always corrupted (ok only the binary ones).

I am having the same problem w/4.1.19. Bueller?


Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com


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

We’re hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

I wish the fix was that simple, but changing the default charset on mysql,
then restarting it, does not fix the problem. I agree that this is looking
more and more like some kind of issue between mysql and rt and agreeing on
what charset things are in.

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Drew Barnes
Sent: Tuesday, May 30, 2006 3:04 PM
To: Covington, Chris
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] binary attachment corruption?

Jesse may have inadvertently touched on this in his response. It looks
like your default character set is utf8, but Jesse suggested doing dumps
with --with-default-charset=binary. Hmmm…2+2= ___ maybe? Anyone
tried setting
[mysqld]
character-set-server = binary
and reporting back?

But I’m still using an old mysql, so I’m not real sure-like.

Covington, Chris wrote:

In my case it is an existing rt installation, that has started to
experience
this problem I believe since the mysql upgrade. New attachments sent in
are
always corrupted (ok only the binary ones).

I am having the same problem w/4.1.19. Bueller?


Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com


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

We’re hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

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

We’re hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html

I suspect existing binary attachments are broken beyond repair. Have
you tried running mysql with binary as the default character set and
sending in a ticket with a new attachment?

Ted Serreyn wrote:

I wish the fix was that simple, but changing the default charset on mysql,
then restarting it, does not fix the problem. I agree that this is looking
more and more like some kind of issue between mysql and rt and agreeing on
what charset things are in.

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

That is how I tested, and no it didn’t help.

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/From: Drew Barnes [mailto:barnesaw@ucrwcu.rwc.uc.edu]
Sent: Wednesday, May 31, 2006 7:13 AM
To: Ted Serreyn
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] binary attachment corruption?

I suspect existing binary attachments are broken beyond repair. Have
you tried running mysql with binary as the default character set and
sending in a ticket with a new attachment?

Ted Serreyn wrote:

I wish the fix was that simple, but changing the default charset on mysql,
then restarting it, does not fix the problem. I agree that this is
looking
more and more like some kind of issue between mysql and rt and agreeing on
what charset things are in.

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

Interesting

mysqldump --all-databases --with-default-charset=binary >dump.test
mysqldump: unknown variable ‘with-default-charset=binary’

mysqldump --version

mysqldump Ver 10.9 Distrib 4.1.19, for pc-linux-gnu (i686)

So I guess that idea don’t work.
Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Drew Barnes
Sent: Tuesday, May 30, 2006 3:04 PM
To: Covington, Chris
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] binary attachment corruption?

Jesse may have inadvertently touched on this in his response. It looks
like your default character set is utf8, but Jesse suggested doing dumps
with --with-default-charset=binary. Hmmm…2+2= ___ maybe? Anyone
tried setting
[mysqld]
character-set-server = binary
and reporting back?

But I’m still using an old mysql, so I’m not real sure-like.

Covington, Chris wrote:

In my case it is an existing rt installation, that has started to
experience
this problem I believe since the mysql upgrade. New attachments sent in
are
always corrupted (ok only the binary ones).

I am having the same problem w/4.1.19. Bueller?


Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com


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

We’re hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

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

We’re hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html

Interesting

mysqldump --all-databases --with-default-charset=binary >dump.test
mysqldump: unknown variable ‘with-default-charset=binary’

My mistake. --default-character-set=name

Ouch, RTFM, --with-default-character-set=binary is the correct syntax.

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Ted Serreyn
Sent: Thursday, June 01, 2006 11:29 AM
To: ‘Drew Barnes’
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] binary attachment corruption?

That is how I tested, and no it didn’t help.

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/

From: Drew Barnes [mailto:barnesaw@ucrwcu.rwc.uc.edu]
Sent: Wednesday, May 31, 2006 7:13 AM
To: Ted Serreyn
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] binary attachment corruption?

I suspect existing binary attachments are broken beyond repair. Have
you tried running mysql with binary as the default character set and
sending in a ticket with a new attachment?

Ted Serreyn wrote:

I wish the fix was that simple, but changing the default charset on mysql,
then restarting it, does not fix the problem. I agree that this is
looking
more and more like some kind of issue between mysql and rt and agreeing on
what charset things are in.

Drew Barnes
Applications Analyst
Raymond Walters College
University of Cincinnati

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

We’re hiring! Come hack Perl for Best Practical:
http://bestpractical.com/about/jobs.html

Interesting

mysqldump --all-databases --with-default-charset=binary >dump.test
mysqldump: unknown variable ‘with-default-charset=binary’

My mistake. --default-character-set=name

Has anyone gotten this sorted out? I’ve upgraded to mysql 5.0.22 and I
still have this problem, so it must be in the configuration somewhere.
I’ve tried starting mysql with --character-set-server=binary and
–default-character-set=binary and the attachments are still corrupted
(with new tickets).

Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com

Has anyone gotten this sorted out? I’ve upgraded to mysql 5.0.22 and I
still have this problem, so it must be in the configuration somewhere.
I’ve tried starting mysql with --character-set-server=binary and
–default-character-set=binary and the attachments are still corrupted
(with new tickets).

I guess what I wrote in June says it all. Still having the problem with
3.6.0 where any and all new binary attachments are corrupted (nothing to
do with dumps).

Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com

Has anyone gotten this sorted out? I’ve upgraded to mysql 5.0.22 and I
still have this problem, so it must be in the configuration somewhere.
I’ve tried starting mysql with --character-set-server=binary and
–default-character-set=binary and the attachments are still corrupted
(with new tickets).

I guess what I wrote in June says it all. Still having the problem with
3.6.0 where any and all new binary attachments are corrupted (nothing to
do with dumps).

I haven’t been able to replicate this at all.

Has anyone gotten this sorted out? I’ve upgraded to mysql 5.0.22 and I
still have this problem, so it must be in the configuration somewhere.
I’ve tried starting mysql with --character-set-server=binary and
–default-character-set=binary and the attachments are still corrupted
(with new tickets).

I guess what I wrote in June says it all. Still having the problem with
3.6.0 where any and all new binary attachments are corrupted (nothing to
do with dumps).

I haven’t been able to replicate this at all.

How can I help reproducing this for you?

Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com

Hello, Chris.
Could you backup your DB, and run next to SQL queries?
ALTER TABLE Attachments CHANGE Content Content LONGBLOB NULL;

Then check if you could binary attachments without problems and report back.
This query is safe but anyway I would suggest you to backup data.On 7/27/06, Covington, Chris Chris.Covington@plusone.com wrote:

On Thu, Jul 20, 2006 at 01:06:01PM -0400, Jesse Vincent wrote:

On Thu, Jul 20, 2006 at 12:54:36PM -0400, Covington, Chris wrote:

On Wed, Jun 07, 2006 at 10:42:23AM -0400, Covington, Chris wrote:

Has anyone gotten this sorted out? I’ve upgraded to mysql 5.0.22 and I
still have this problem, so it must be in the configuration somewhere.
I’ve tried starting mysql with --character-set-server=binary and
–default-character-set=binary and the attachments are still corrupted
(with new tickets).

I guess what I wrote in June says it all. Still having the problem with
3.6.0 where any and all new binary attachments are corrupted (nothing to
do with dumps).

I haven’t been able to replicate this at all.

How can I help reproducing this for you?


Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com


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

We’re hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html

Best regards, Ruslan.

Hello, Chris.
Could you backup your DB, and run next to SQL queries?
ALTER TABLE Attachments CHANGE Content Content LONGBLOB NULL;

Then check if you could binary attachments without problems and report back.
This query is safe but anyway I would suggest you to backup data.

Ruslan,

Thank you very much! Attachments work perfectly now!

Chris Covington
IT
Plus One Health Management
75 Maiden Lane Suite 801
NY, NY 10038
646-312-6269
http://www.plusoneactive.com

Ruslan, this solution also resolved the attachment issue here under rt
3.6.0. Is this going to be a permanent change to the schema?

Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/

Hello, Chris.
Could you backup your DB, and run next to SQL queries?
ALTER TABLE Attachments CHANGE Content Content LONGBLOB NULL;

Then check if you could binary attachments without problems and report back.
This query is safe but anyway I would suggest you to backup data.

yep, when we merge 3.5-EXPERIMENTAL-MYSQL-UPDATES
(http://svn.bestpractical.com/cgi-bin/index.cgi/bps/browse/rt/branches/)
into 3.6On 7/28/06, Ted Serreyn ted@serreyn.com wrote:

Ruslan, this solution also resolved the attachment issue here under rt
3.6.0. Is this going to be a permanent change to the schema?


Ted Serreyn Phone: 262-432-0260 Fax: 262-432-0232
Serreyn Network Services, LLC http://www.serreyn.com/

Hello, Chris.
Could you backup your DB, and run next to SQL queries?
ALTER TABLE Attachments CHANGE Content Content LONGBLOB NULL;

Then check if you could binary attachments without problems and report back.
This query is safe but anyway I would suggest you to backup data.

Best regards, Ruslan.

Hello - I’m resurrecting this old thread from July 2006. Our RT has
been eating binary attachments for a little while now, and I’ve just now
gotten around to debugging why. It turns out that it’s related to the
MySQL datatype that was being used for the Content column in the
Attachments table:

Ruslan Zakirov (Wed Jul 26 16:49:44 EDT 2006):

Could you backup your DB, and run next to SQL queries?
ALTER TABLE Attachments CHANGE Content Content LONGBLOB NULL;

and Fri Jul 28 14:32:00 EDT 2006:

yep, when we merge 3.5-EXPERIMENTAL-MYSQL-UPDATES
(http://svn.bestpractical.com/cgi-bin/index.cgi/bps/browse/rt/branches/)
into 3.6

Ruslan, this solution also resolved the attachment issue here under rt
3.6.0. Is this going to be a permanent change to the schema?

This did fix the problem for us - any new attachments I’ve sent through
have come back totally fine. When I browse into that 3.5-EXPERIMENTAL
branch, I can see that that SQL fix is included in schema.mysql-4.1 [1],
which hasn’t been merged in as of 3.6.4.

Anyone know when this is making it into the mainline? Since this hasn’t
been reported very often, I’m guessing that it’s probably some strange
charset issue with MySQL, but it’d be nice to have it in there.

-CJ

[1]
http://svn.bestpractical.com/cgi-bin/index.cgi/bps/view/rt/branches/3.5-EXPERIMENTAL-MYSQL-UPDATES/etc/schema.mysql-4.1