[Rt-devel] mysqldump on Attachments table (bug #6655)

[Gavin McTaggart]

Has any thought been given to RT bug #6655?   I have searched the 
archives, but can't find any mention of anyone solving this problem (or 
even *having* the same issue, apart from the original bug submitter).

The problem is that the Attachments table uses the LONGTEXT data type to
store binary data.  While this works, mysqldump does not correctly save
the data.  I noticed this when migrating a RT database between servers.
The migration appeared to work, but a short time later, we noticed that
existing binary attachments were corrupted (new ones were fine).
Luckily, the old server was still available and I was able to overwrite
the bad data on the new, live server.

It looks as though the fields were originally LONGBLOB, but were changed
somewhere in RT2 to LONGTEXT to give case-insensitive searches.
See:
http://lists.bestpractical.com/pipermail/rt-users/2001-December/005865.html

This has also been raised as a bug with mysql, but they appear to have
washed their hands of it, even though I disagree with their reasoning
http://bugs.mysql.com/bug.php?id=10249.


I am running RT V3.4.2 and mysql 4.1.1, and am performing two mysqldumps
to correctly back-up the RT database.  One backs up the entire database
and another backs up just the Attachments table (using
--compatible=ansi), which seems a decidedly suboptimal arrangement.
Then again, I have been living with it for months now, so it isn't that
high on my priority list.  :)


After all that, I hope that I haven't missed something obvious though.




Gavin