mysqldump, BLOBs, and the null character

[Greg.Harabedian]

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?
I should say that I seem to only see this problem with the NULL
character (ascii=0).

Thanks,
Greg

[Greg.Harabedian]

Well, I figured out the answer to my own problem. It seems that
mysqldump escapes botht 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

Gr*************@gmail.com wrote:

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?
I should say that I seem to only see this problem with the NULL
character (ascii=0).

Thanks,
Greg