Is there any magic I need to do to dump/restore?
I’m missing all the images - they’re broken now.
George Barnett
Reality Engineer
m: (+44) 797 457 1868
e: george@alink.co.za
“Cogito ergo I’m right and you’re wrong.”
– Blair Houghton
George Barnett wrote:
Is there any magic I need to do to dump/restore?
I’m missing all the images - they’re broken now.
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.
Apparently, the change was made back in RT 2 from LONGBLOB to LONGTEXT
for case insensitive searching.
The solution to this is to add --compatible=ansi which will allow for a
usable MySQL backup of the database without borked binary attachments.
Cheers,
George
George Barnett
Reality Engineer
m: (+44) 797 457 1868
e: george@alink.co.za
The universe seems neither benign nor hostile, merely indifferent.
– Sagan
George Barnett (george@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:
[Rt-devel] mysqldump on Attachments table (bug #6655)
[Rt-devel] mysqldump on Attachments table (bug #6655)
[Rt-devel] mysqldump on Attachments table (bug #6655)
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
George Barnett wrote:
Is there any magic I need to do to dump/restore?
I’m missing all the images - they’re broken now.
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.Apparently, the change was made back in RT 2 from LONGBLOB to LONGTEXT
for case insensitive searching.
I have switched it back to LONGBLOB (MySQL 4.1, UTF-8 default encoding
for the RT database), as it was the only (quick) solution to make it
work again.
I have lost in the process all accents and past binary attachments,
but now new attachments are readable right after being attached.
Regards,
J.
Jérôme Fenal - jfenal AT gmail.com - http://fenal.org/
Paris.pm - http://paris.mongueurs.net/
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:
[Rt-devel] mysqldump on Attachments table (bug #6655)
[Rt-devel] mysqldump on Attachments table (bug #6655)
[Rt-devel] mysqldump on Attachments table (bug #6655)
[rt-users] CLI and groups
[rt-users] RTx installer and package system??
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 —
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:
[Rt-devel] mysqldump on Attachments table (bug #6655)
[Rt-devel] mysqldump on Attachments table (bug #6655)
[Rt-devel] mysqldump on Attachments table (bug #6655)
[rt-users] CLI and groups
[rt-users] RTx installer and package system??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-logType ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> CREATE TABLE
attachments(
→idint(11) NOT NULL auto_increment,
→Contentlongtext,
→ 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-logType ‘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.
I would like to unsubscribe to the mailing list.
Des m�canismes de contr�le parental puissants permettent � votre enfant de
d�couvrir tout ce qu�Internet a � offrir.
http://join.msn.com/?pgmarket=fr-ca&page=features/parental Commencez d�s
maintenant � profiter de tous les avantages de MSN Premium et obtenez les
deux premiers mois GRATUITS*.
lol, here is something from the welcome mail i received after joining:
[QUOTE]
If you ever want to unsubscribe or change your options (eg, switch to
or from digest mode, change your password, etc.), visit your
subscription page at:
[/QUOTE]
Yen-Nhi Vo wrote:
Hi,
Recently I update our old RT-3.0.1 to 3.8.1. Everything working fine but
when I check the attachments then i see unicode. my old mysql 4.1.12 and the
default character set was latin1 and the new mysql server is 5.0.26, default
character set is utf8. I dumped old Attachemnts table and restore to new
database with default character set utf8. When I click the attachment doc
format file then I see unicode. Need help how to solve this problem.
Thanks
Kazi
View this message in context: http://www.nabble.com/MySQL-dump-of-database-now-attachments-dont-display-tp19333136p19333136.html
Consult with UPGRADING.mysql, if something is still unclear after that
then return back to us with more questions.On Fri, Sep 5, 2008 at 8:22 PM, KaziNeel kazineel@gmail.com wrote:
Hi,
Recently I update our old RT-3.0.1 to 3.8.1. Everything working fine but
when I check the attachments then i see unicode. my old mysql 4.1.12 and the
default character set was latin1 and the new mysql server is 5.0.26, default
character set is utf8. I dumped old Attachemnts table and restore to new
database with default character set utf8. When I click the attachment doc
format file then I see unicode. Need help how to solve this problem.
Thanks
KaziView this message in context: http://www.nabble.com/MySQL-dump-of-database-now-attachments-dont-display-tp19333136p19333136.html
Sent from the Request Tracker - User mailing list archive at Nabble.com.
Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.comDiscover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Best regards, Ruslan.
I follow the UPDATE document. Everything working fine except binary
attachments breakage. When I click the doc or excel file then I see binary.
Can you help me how to solve this problem?
Thanks
Kazi
KaziNeel wrote:
Hi,
Recently I update our old RT-3.0.1 to 3.8.1. Everything working fine but
when I check the attachments then i see unicode. my old mysql 4.1.12 and
the default character set was latin1 and the new mysql server is 5.0.26,
default character set is utf8. I dumped old Attachemnts table and restore
to new database with default character set utf8. When I click the
attachment doc format file then I see unicode. Need help how to solve this
problem.
Thanks
Kazi
View this message in context: http://www.nabble.com/MySQL-dump-of-database-now-attachments-dont-display-tp19333136p19336034.html