Commavee

This is a short and sweet tech tip. If you have binary data, such as images or zip files, stored in a mysql database, this technique will export those blobs (binary large objects) back to the file system.

Case:

Bugzilla attachments exist in mysql that now must be exported to another bug tracking system.

Approach:
Run this query

select thedata from attachments where bug_id='224' and mimetype not like 'text/plain' and mimetype not like 'text/hmtl' into dumpfile '/data/bug-224-attachment.zip';

for each bug that contains a binary attachment. You can extend this approach by writing a script that queries for all bugs with attachments, then acts only on those those that are binary attachments. Since you may wish to include the file type attachment in the dumpfile name, consider adding an additional loop that checks for the mimetypes that correspond to each of the various binary formats (jpg, zip, etc.). Pass both the bug_id and the mimetype into the filename that you create in the dumpfile.

Tags: freepository, tech tips, mysql