mysqldump, BLOBs, and the null character

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'll start off by saying I am using MySQL v4.0 and my question
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

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


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


Re: mysqldump, BLOBs, and the null character

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:

That's it.
Greg wrote:
Quoted text here. Click to load it

Site Timeline