[rt-users] BUG: MySQL dump of database now attachments dont display

[Duncan McEwan]

George Barnett (george at alink.co.za) wrote:

> For the Google cache (and anybody else backing up by mysqldump):
> 
> When doing a standard MySQL mysqldump (mysqldump -u -p rt3), the
> Attachments table will be broken when reimported.  This will result in
> broken or corrupted binary files, such as jpeg, tiff, etc.

So this was mentioned on rt-devel last week as well.  See the following
messages in the archives:

  http://lists.bestpractical.com/pipermail/rt-devel/2005-December/007908.html
  http://lists.bestpractical.com/pipermail/rt-devel/2005-December/007909.html
  http://lists.bestpractical.com/pipermail/rt-devel/2005-December/007910.html

I'd been meaning to follow up in that thread but didn't get around to it, so
I'll do it here instead!

> The solution to this is to add --compatible=ansi which will allow for a
> usable MySQL backup of the database without borked binary attachments.

In the 2nd of the messages mentioned above Jesse says that as an alternative
to the --compatible=ansi, you can also '... export with charset "binary"',
but gives no further information on how to do that.  I'm guessing that you
do this using the mysqldump --default-character-set=binary option, but I'd
like to know for sure...

Also I checked out the mysql bug report mentioned in the first of the messages
above (http://bugs.mysql.com/bug.php?id=10249) and saw the comment that
--compatible=ansi may not be such a good workaround since the resulting
dumpfile doesn't contain certain table attributes (such as the engine type).

So now I'm confused as to whether the best workaround is to use the
--compatible=ansi or Jesse's suggestion, and if the latter, whether this is
done using the --default-character-set=binary option or some other way.

But I'm even more confused by the fact that when I tried the steps given in the
mysql bug report mentioned above (obviously adjusting for different pathnames
and also adding a missing ';' on to the end of one of the select commands) I
couldn't duplicate the problem!  Ie: the binary files extracted from my test
database were identical regardless of whether I dumped it with one or other or
neither of the options mentioned earlier.

So now I'm wondering if the problem has been addressed in newer versions of
mysql, despite the seeming lack of interest in doing so by the mysql
developers.  Our server is 4.1.15-log and the mysqldump is ver 10.9, running
on an i386 NetBSD system.

Are others reproduce the problem using the steps given in the mysql bug report
or not?

Can anyone shed any further light on this?

Thanks,

Duncan