mysqldump and blobs 1

[gharabed]

I'll start off by saying I am using MySQL v4.0 and my question is...how do I get mysqldump to dump the actual binary values store in a blob? Here is an example:

-- Create a test table
create table dummy(col1 blob);
-- Next insert a null ascii character (0)
insert into dummy values (char(0));
-- To verify there is actually something there type:
select length(col1) from dummy;
-- You should see a value of "1" reprenting the null char
-- Now, dump the dummy table using mysqldump
mysqldump --tab=/tmp <db_name> dummy
-- Now look at the content of /tmp/dummy.txt and you will
-- see that the contents are a literal "/0" (no quotes of
-- course). If you do an octal dump:
od -c /tmp/dummy.txt
-- you get:

0000000 \ 0 \n
0000003

-- showing the literal \ and 0 (along with a newline)

If you use the "select into dumpfile" command you get the actual binary value. For example if you type:

select col1
into dumpfile '/tmp/dummy.dat'
from dummy;

and then do an octal dump on the resulting file (od -c /tmp/dummy.dat) you get:

0000000 \0
0000001

Indicating a single byte/character that is the null character.

How do I get all of the binary data in my blob columns to dump using mysqldump?

Thanks,
Greg

 

[ericbrunson]

If you import the file from mysqldump, do you get the correct information in your blob column? My thought is, the information is there, just not necessarily in the format you expect it to be, but in the correct format for in insert statement to put it back.

If the table is not recreated correctly, you may have found a bug in mysqldump and should log it in bugzilla along with your test case.

 

[gharabed]

Well, I figured out the answer to my own problem. It seems that mysqldump escapes both the null, tab, and newline characters when dumping them to a file. If you want to get a pure dump with no escape character (/) and the blob appearing in the dump file exactly as it is stored then you must supply mysqldump the command line option: --fields-escaped-by=""

That's it.
Greg