|
Home > Archive > MySQL Server Forum > August 2005 > mysqldump, BLOBs, and the null character
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
mysqldump, BLOBs, and the null character
|
|
| Greg.Harabedian@gmail.com 2005-08-20, 3:23 am |
| 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@gmail.com 2005-08-22, 3:23 am |
| 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
Greg.Harabedian@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
|
|
|
|
|