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

[Ruslan Zakirov]

On 1/11/06, Duncan McEwan <duncan at mcs.vuw.ac.nz> wrote:
> 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?
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.

>
> 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.
Really RT uses text types wrong according to changes in MySQL 4.1,
because it stores both binary and text in the same column that's not
allowed anymore with UTF-8. UTF-8 has not allowed byte sequences and
mysql throws errors, you can fool mysql if you set encoding to
'latin-1' because it allows you to use any bytes you want.

'latin-1' trick is not the right solution, because all people in non
latin world would be hitting sort and may be other bugs, so we need
other solution:
* new schema for MySQL 4.1
** 4.0/4.1-old -> 4.1-new upgrade script
** backport Handle::Versions patch from Jifty::DBI to SB
* implement DB server versions checks into RT schema installer

>
> 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)
try UTF-8 charset, as you can see from RT schema there is no CHARSET
specified, because MySQL 4.0 doesn't support such syntax and RT uses
this schema for all MySQL verssions.

>
> mysql> insert into attachments (content)
>     -> values(load_file('/tmp/mysql-test/Picture1.png'));
> Query OK, 1 row affected (0.01 sec)
try file with wrong UTF-8 sequences

>
> 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 ---

--
Best regards, Ruslan.