[rt-users] 3.6.0rc3/mysql: reinserting image attachments into the DB?

[Joshua Colson]

> My question: I still have all the emails from which those image
> attachments were culled. Does anyone have a suggestion for how to
> reinsert the uncorrupted images into the database in place of the
> useless blobs currently residing there? (I'm no DBA at the best of
> times, and I've never needed to work with non-textual data in mysql
> before; I'm feeling a bit lost...)

RT does not store attachments as BLOBs. All binary attachments are
converted to base64 encoding before insertion into the database. What you
want to do should be possible, but it will likely require a fair amount of
work to accomplish. You say that you aren't a DBA, but if you can run some
sql updates, you can fix your problem. First, you'll need to extract all
the images from your emails, then you'll have to figure out which image
goes with which ticket. This will be the hard part. The following SQL run
from the mysql command line tool should help pull the relevant information
together.

select
 a.id as "Attachments.id",
 a.Filename as "Attachments.Filename",
 tr.id as "Transactions.id",
 t.id as "Tickets.id",
 t.Subject as "Tickets.Subject"
FROM
 Attachments a,
 Transactions tr,
 Tickets t
WHERE
 a.ContentType <> 'text/plain'
  and
 a.Filename is not NULL
  and
 a.TransactionId = tr.id
  and
 tr.ObjectType = 'RT::Ticket'
  and
 tr.ObjectId = t.id;


With that information in hand, you should be able to cross reference the
images you've extracted from the emails to the
tickets/transactions/attachments records in the database. Then, you'll
need to use some external utility such as perls MIME::Base64 module to
convert the images to base64 format. Then simply update the record with
the new data.

I hope I explained that clearly enough to get you where you would like to be.

Good luck, and make sure you back the system up before doing anything that
I've suggested! I should also mention that I have not tested any of this,
but it should all work, in theory.

--
Joshua Colson <jcolson at voidgate.org>