Binary attachment corruption on database move

Has anyone else experienced corruption of binary attachments when moving
their RT database from one host to another?

Long version: I used mysqldump to export the rt3 db from a host running
MySQL Max 4.1.13 to a machine running the RHEL MySQL 4.1.20. The my.cnf
file was the same on both hosts, so I’m not too sure what could have
happened, but now it appears that all the binary attachments in RT have
been corrupted in some way. The RT frontend is still the exact same as
before.

thanks in advance,

-jkl

Yes I had this same problem. I updated the wiki with the fix I had
found elsewhere:

http://wiki.bestpractical.com/index.cgi?MigrateToNewServer

See my "Note: I found that with the above command my binary attachments"
section.

Tim Casada

Has anyone else experienced corruption of binary attachments when moving
their RT database from one host to another?

There have been several threads on this subject on the mailing lists.
Search the rt-devel archives for Dec 05 for the subject “mysqldump on
Attachments table (bug #6655)” and the rt-users archives for Jan 06 for
the subject “BUG: MySQL dump of database now attachments dont display”

I posted to both of those threads to say that I’d tried to reproduce the
described problem by dumping and then restoring a test database but had been
unable to. I was concerned that my testing might be incorrect and that if
I ever had to do a real restore I’d find out the hard way that we did
suffer from the problem :frowning:

Since that time I have had occasion to dump and restore my complete RT
database containing ~20000 tickets and found no corruption of binary
attachments (word documents or jpgs).

Ruslan did post in the 2nd of the threads mentioned above (message ID
589c94400601112215i878350cw4064659ea4510a8c@mail.gmail.com) saying:

Yep. I think that’s because this problem hits only people on MySQL 4.1
with default server charset UTF-8, but this is small amount of users.

I recommend to use ‘latin-1’ encoding for RT database in MySQL 4.1.

Also people can convert almost all *TEXT columns to *BLOB, as tests
shows this helps and RT works correctly with MySQL with UTF-8 as
default charset.

The settings for my mysql database are

character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8

Both utf8 and latin-1 are mentioned there and I’m not sure what the various
settings are used for. But since I don’t appear to be affected by the
binary attachments corruption problem I must be following Ruslan’s advice!

Duncan