| Bug #10249 | mysqldump writes incorrect sql for longtext field | ||
|---|---|---|---|
| Submitted: | 28 Apr 2005 22:25 | Modified: | 1 May 2005 10:12 |
| Reporter: | Carl Karcher | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 4.1.11-standard | OS: | Linux (Fedora Core (2.6.10_1.771_FC2)) |
| Assigned to: | Sergei Golubchik | CPU Architecture: | Any |
[1 May 2005 7:02]
Jorge del Conde
Thanks for your bug report. Verified w/4.1.12 from bk: jorge-/home/jorge> diff test_vrent.png /tmp/mitest.png Binary files /home/jorge/test_vrent.png and /tmp/mitest.png differ
[1 May 2005 10:12]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: You have to use BLOB type for binary data
[4 May 2005 20:15]
Carl Karcher
While I understand that it's not really a bug, I'd like to request that it be very strongly documented. Anything (even user error) that can result in silent corruption should be strongly documented. I've since found that using the --compatible=ansi is not a workaround since that doesn't save certain table attributes such as storage engine. I cound't find any documenation that would explain that. I did submit the issue to Best Practical, the vendor who supplied the schema for the application (RT), suggesting the field should be changed to longblob from longtext.
[11 May 2006 20:01]
[ name withheld ]
pardon - this is NOT a bug ? the user has to pay for that (corrupt all data without knowing it) because the developer stores the data in somewhat "exotic" form ? jesus! if i do a mysqldump for backup (thousands of people do) - the least what i need and what i expect is that binary data silently gets corrupted. this one reproduceably corrupts all Attachments of my RT installation: mysqldump rt3 >/tmp/rt3.dump mysql rt3 </tmp/rt3.dump this seems to avoid corruption (found this by chance) : mkdir /tmp/backup chmod 777 /tmp/backup mysqldump --tab /tmp/backup rt3 cd /tmp/backup mysql rt3 < Attachments.sql mysqlimport --local rt3 Attachments.txt i also think this bug is SERIOUS. furthermore i was going through a real upgrade/migration-hell just because of this. i can understand the point here and the message you want to tell: developers - please store data in the right format! - but - at least, if you won`t fix that, please make mysqldump print a big warning message to stderr about this issue! (on export&import) i`m on SuSE10, 2.6.13-15.8-default with mysql-4.1.13-3 thank you! regards roland k. systems engineer
Description: When using mysqldump (Ver 10.9 Distrib 4.1.11, for pc-linux-gnu) to export a db with binary values stored in a LONGTEXT to a file then reading (source) the file with mysql, data in the longtext field is corrupted. Repeating the same test using the --compatible=ansi option for mysqldump does not corrupt the data. While I understand that using a longtext field for binary data is a questionable design, the db schema we encountered this on is supplied by a vendor (Best Practical's RT). The problem does not occur when the same data is stored as a longblob field. How to repeat: First I'll show that using mysqldump with the --compatible=ansi option preserves the data then show that not using it corrupts the data in the field "content": mysql> use testme; Database changed 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.03 sec) mysql> insert into attachments (content) values(load_file('/home/karcher/test_vrent.jpg')); mysql> \! mysqldump --compatible=ansi -u root -p -r testme_ansi.sql testme mysql> \. testme_ansi.sql mysql> select content into dumpfile '/tmp/test_vrent.jpg' from attachments mysql> diff test_vrent.jpg /tmp/test_vrent.jpg mysql> \! mysqldump -u root -p -r testme.sql testme mysql> \. testme.sql mysql> select content into dumpfile '/tmp/test2_vrent.jpg' from attachments; mysql> \! diff test_vrent.jpg /tmp/test2_vrent.jpg Binary files test_vrent.jpg and /tmp/test2_vrent.jpg differ As shown, when mysqldump writes output without the --compatible=ansi option and the sql is imported, the value of the field "content" is corrupted. Binary file test_vrent.jpg to be supplied. Suggested fix: 1. Fix mysqldump to write correct sql without the --compatible=ansi 2. Have mysqldump issue a error when writing a longtext field with binary data (how would you tell?) This is a serious issue given that mysqldump is how many people backup or export/import their db's and would result in silent corruption should the db need to be restored.