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

[Duncan McEwan]

OK ... looks like I'll have to follow up to my own email from before 
Christmas, since apart from one reply directly to me, no else has done so!

It seems odd to me that a thread about possible corruption of an RT database
restored from a mysqldump hasn't attracted more attention!  Are others using
some other method of backup?

To recap: this thread concerned the fact that there is apparently a bug in the
way mysqldump deals with fields of type longtext (or from a different point of
view: a bug in the way RT uses fields of type longtext to store attachments 
that could potentially contain binary data).  So database backups made using 
mysqldump and then later restored would have corrupted attachments.

The following messages in the RT archives contain more details about this:

  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
  http://lists.bestpractical.com/pipermail/rt-users/2005-December/036161.html
  http://lists.bestpractical.com/pipermail/rt-users/2005-December/036174.html

Also, the mysql "bug" is described at http://bugs.mysql.com/bug.php?id=10249.

In my previous message (the 036174.html one listed above) I said that I had
been unable to duplicate the problem that had been described.  That is, when
I used mysqldump to dump a database with a longtext field that contains binary
data, and then I restored that dump, the binary data was NOT corrupted.
The one response I got (that didn't go to the list) was from a person who
had seen the attachment corruption problem and they didn't know why I
wasn't seeing it.

So, if I'm not seeing the problem, why do I care?  Well, if others ARE seeing
it, then it's possible that I'm overlooking something in my testing.  And
I'd really like to be sure that my backups are being done in a way that can
be restored if required, rather than finding out when I really need to use
a backup that they weren't!

The following shell command trace shows: (1) a database with a longtext field
being created; (2) binary data being loaded into it; (3) a dump being made;
(4) the dump being restored; and (5) the binary data being extracted and
shown to be identical to the original data.  This is all without options
like "--compatible=ansi" having to be used on the mysqldump command line as
described in the earlier postings listed above.

So, if anyone can tell me whether I'm overlooking something in the following
commands (or even just show the same command sequence with attachment
corruption happening so that I know there is definitely a problem I have to
track down) I'd be very grateful!

Thanks,

Duncan


--- Start of shell commands ---

circa# pwd
/tmp/mysql-test
circa# ls -l
total 51
-rw-r--r--  1 root  wheel  51665 Jan 11 14:55 Picture1.png
circa# file Picture1.png
Picture1.png: PNG image data, 428 x 400, 8-bit/color RGB, non-interlaced
circa# mysqladmin create mysql-test
circa# mysql mysql-test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3762 to server version: 4.1.15-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `attachments` (
    -> `id` int(11) NOT NULL auto_increment,
    -> `Content` longtext,
    -> PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into attachments (content)
    -> values(load_file('/tmp/mysql-test/Picture1.png'));
Query OK, 1 row affected (0.01 sec)

mysql> select content into dumpfile '/tmp/mysql-test/Picture2.png' from attachme
nts;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
circa# ls -l
total 102
-rw-r--r--  1 root   wheel  51665 Jan 11 14:55 Picture1.png
-rw-rw-rw-  1 mysql  wheel  51665 Jan 11 15:03 Picture2.png
circa# mysqldump mysql-test > mysql-test.sql
circa# ls -l
total 185
-rw-r--r--  1 root   wheel  51665 Jan 11 14:55 Picture1.png
-rw-rw-rw-  1 mysql  wheel  51665 Jan 11 15:03 Picture2.png
-rw-r--r--  1 root   wheel  84267 Jan 11 15:04 mysql-test.sql
circa# mysqladmin drop mysql-test
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'mysql-test' database [y/N] y
Database "mysql-test" dropped
circa# mysqladmin create mysql-test
circa# mysql mysql-test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3768 to server version: 4.1.15-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source mysql-test.sql
Query OK, 0 rows affected (0.00 sec)

[11 identical lines omitted]

Query OK, 1 row affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

[7 identical lines omitted]

Query OK, 0 rows affected (0.00 sec)

mysql> select content into dumpfile '/tmp/mysql-test/Picture3.png' from attachme
nts;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
circa# ls -l
total 236
-rw-r--r--  1 root   wheel  51665 Jan 11 14:55 Picture1.png
-rw-rw-rw-  1 mysql  wheel  51665 Jan 11 15:03 Picture2.png
-rw-rw-rw-  1 mysql  wheel  51665 Jan 11 15:05 Picture3.png
-rw-r--r--  1 root   wheel  84267 Jan 11 15:04 mysql-test.sql
circa# file Picture*
Picture1.png: PNG image data, 428 x 400, 8-bit/color RGB, non-interlaced
Picture2.png: PNG image data, 428 x 400, 8-bit/color RGB, non-interlaced
Picture3.png: PNG image data, 428 x 400, 8-bit/color RGB, non-interlaced
circa# cmp -l Picture1.png Picture2.png
circa# cmp -l Picture1.png Picture3.png
circa# 

--- End of shell commands ---