3.6.0rc3/mysql: reinserting image attachments into the DB?

Background: Startups being what they are, I had to shove my RT
installation out the aircraft door long before I felt ready, hoping the
chute was in working order… I don’t currently have hardware for a test
system; when I have to tweak RT, I’m sometimes forced to do a mysqldump
and then restore from the dump after testing changes. After one such
dump/change/test/restore sequence, I noticed that any images attached to
existing tickets had been corrupted, probably because before today I’d
not thought to specify the “-O --hex-blob=TRUE” option to mysqldump. If
you’re curious as to what happened to the images, a sample
before-and-after:
http://www.cs.umass.edu/~olc/original-email-image.jpg
http://www.cs.umass.edu/~olc/corrupted-RT-image.jpg

(latter image has been GIMPed to obfuscate of innocent bystanders and
their support requests, ignore the black blotches. It’s the psychedelia
that’s the problem.)

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…)

Thanks,
	Ole

/Ole Craig
Security Engineer

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .

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@voidgate.org

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.
[…]

    Interesting. Before I'd read your message, I tried the

following:

mysql> update Attachments set Content=LOAD_FILE(‘/tmp/Outlook.jpg’) where id=323;

    ...which seems to have achieved the desired results at least as

far as that particular attachment is concerned. (I.e. it now displays
correctly in the ticket.) I noticed (belatedly) that the type for
Attachments.Content is “longtext” – does mysql have an autoconversion
feature for binary data that happens to DTRT? Or is RT robust enough to
handle the fact that the DB object is not base64? I don’t see any errors
in the RT log pertaining to this…

    Thanks for the SQL, by the way -- since I had less than 10

images in the system, I was going to go the BFMI route for finding the
relevant attachment numbers, but your way is much more elegant.

(BFMI == “Brute force, massive ignorance” :slight_smile:
/Ole Craig
Security Engineer

303-381-3802 (main support hotline)
303-381-3824 (my direct line)
303-381-3801 (fax)

www.stillsecure.com
. . .

    Interesting. Before I'd read your message, I tried the

following:

mysql> update Attachments set Content=LOAD_FILE(‘/tmp/Outlook.jpg’) where id=323;

    ...which seems to have achieved the desired results at least as

far as that particular attachment is concerned. (I.e. it now displays
correctly in the ticket.) I noticed (belatedly) that the type for
Attachments.Content is “longtext” – does mysql have an autoconversion
feature for binary data that happens to DTRT? Or is RT robust enough to
handle the fact that the DB object is not base64? I don’t see any errors
in the RT log pertaining to this…

That surprises me. I’m certainly not a MySQL expert, but I find it
incredibly surprising that loading binary data into a text field works
on-the-fly. However, if MySQL does to on-the-fly conversion, it would
make sense that it chooses base64.

As far as I know, RT relies on the data in the DB being valid (that is,
it does not attempt to auto-convert it).

I’m glad you got it fixed, no matter what worked for you.

Joshua Colson jcolson@voidgate.org