Monday, July 14, 2008

Blob Attachment data corruption and MySQL Query Browser

For test purpose, you just created a table in you database which holds a blob image value. You upload an image to it manually by editing the table using MYSQL Query Browser and browse to the image and save it. You then generated a sql script using mysqldump --hex-blob ... > table.sql for that table. You drop the table and wish to restore it (you generally don't want to drop tables like that but this is for test purpose only). You attempt to restore the table my using mysql .... <>. You try to use the table or simply try preview it and find out that the data is not an image anymore.

There seems to be an issue when you load images on to table via mysql manually. The restored table has the image data corrupted. I'm not entirely sure what caused it to happen. User error? May be!!

So, instead of loading the image data manually, I used LOAD_FILE("../images/image.png") to fill up the table. Then, try repeating the process mysqldump, dropping the table and then trying to restoring it. This worked for me.

I could now use the table.sql script to load images in any table in any other system. This might be useful for static image data.

This can also be useful to see if your backup process/procedure in fact work. Sure there are other tools for such an important task but this might come handy for small tasks that might come around.