469,266 Members | 1,679 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,266 developers. It's quick & easy.

mysqldump, BLOBs, and the null character

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

Aug 20 '05 #1
1 4335
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


Aug 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by ehabaziz2001 | last post: by
3 posts views Thread by Danny Tuppeny | last post: by
AmberJain
2 posts views Thread by AmberJain | last post: by
4 posts views Thread by jameskuyper | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.