Mass export of attachments from bugzilla

Question

Our bigzilla installation has a few GB of data and no server admin. I have web administrative access to bugzilla and would like to get all of the attachments (with their original name and bug #).

I know there's a mass export of database as XML, but presumably the attachments would have to be parsed afterwards.

I can request access to server and then take a look into attachments table in the bug database, but then, again, I will have to somehow decode the attachment data. So,

how to export all of the Bugzilla's bug attachments at once, as files?

Perhaps some desktop client has this functionality? Or someboday has a script to create a tarball?

Answer 1

I used the below to build a separate sql file that can be executed to get the files out. tip: watch for special chars in the filename

select concat('SELECT ad.thedata into DUMPFILE  \'e:/temp/attachments/'
, a.bug_id 
, '___'
, ad.id
, '___'
, replace(a.filename,'\'','')
, '\'  FROM bugs.attachments a, bugs.attach_data ad where ad.id = a.attach_id'
, ' and ad.id = '
, ad.id
,';') into outfile 'C:/Temp/attachments.sql'
from bugs.attachments a, bugs.attach_data ad where ad.id = a.attach_id;

Answer 2

I needed to see if there was already a script for the very same requirement and came up here. Since I didn't understand the pure SQL answer, I went on to create my own Python script. If somebody needs it in future, here it is,

The script will create directories based on the column name that is given as the argument. I'm not checking the argument since this is a quick and dirty script.

[wizard @ work]$ ./fetch_attach_from_bugzilla.py bug_id
[wizard @ work]$ ls
16  18  19  21  23  24  26 fetch_attach_from_bugzilla.py 
[wizard @ work]$

[wizard @ work]$ cat fetch_attach_from_bugzilla.py
#!/usr/bin/python

import binascii;
import MySQLdb;
import os;
import sys;

conn = MySQLdb.connect(host='',user="",passwd="",db="bugs");

r = conn.cursor();
r.execute('select bug_id from bugs');

bug_ids = r.fetchall();

for bug_row in bug_ids:
   bug_id = bug_row[0];
   cmd = 'select attachments.filename,attachments.mimetype, bugs.' + sys.argv[1] + ', attach_data.thedata from attachments left join attach_data on attachments.attach_id = attach_data.id left join bugs on bugs.bug_id = attachments.bug_id where attachments.bug_id = ' + str(bug_id);
   r.execute(cmd);

   for adata_row in r.fetchall():
      if not os.path.isdir(os.path.join(os.getcwd(),str(adata_row[2]))):
         os.mkdir(str(adata_row[2]));
      f = open(os.path.join(os.getcwd(),str(adata_row[2]),adata_row[0]), "wb+")
      f.write(adata_row[3]);
      f.close();


r.close;
conn.close;
[wizard @ work]$