Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Warren Baker (warrendecoy.co.za)
Date: Wed Sep 10 2008 - 14:05:36 CDT
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
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.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql