MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Warren Baker Date:September 10 2008 7:05pm
Subject:BLOB and restore
View as plain text  
HI all,

I have inherited a large problem. We have a database that stores
customer details in various tables. One of the fields in the table is
of type mediumblob.

Unfortunately there was a disk failure. The backups of the database
were been done by the mysqldump command. The exact command that was
used was incorrect and it also did not take the blob into account.

The dump command was as follows:

mysqldump -l -F -e -u root -p<password> --fields-terminated-by=,
--fields-enclosed-by=\\\ --quote-names $database -T $BACKUPDIR

Unfortunately this resulted in Mysql adding escape characters to the
binary data and also enclosing all fields with " --quote-names" for
example: "--quote-namesT1|1|test.htm --quote-names,"

I have managed to load the data into mysql (using both mysqlimport and
LOAD DATA) but my understanding is that these commands dont support
blobs(?) so not sure how else to do it.
Using LOAD DATA infile ... enclosed by ' --quote-names ' doesnt work
as it needs to be a single character. So once I got the data loaded
using just load data local infile .. into table <tablename> terminated
by ',';

I then used the SQL query:
update table set fieldname=(replace(fieldname,' --quote-names',''));

to get rid of the --quote-names from enclosing the data in each field.

So my question is as follows, and don't laugh, is there by any chance
that there is some magical step/tool that I could do to restore this
data and that the binary data is restored correctly to its original

Mysql v5.0.45 using MyISAM.

BLOB and restoreWarren Baker10 Sep