Skip to main content

Raymii.org Logo (IEC resistor symbol)logo

Quis custodiet ipsos custodes?
Home | About | All pages | RSS Feed | Gopher

RT (Request Tracker) - find and delete big attachments

Published: 17-01-2013 | Author: Remy van Elst | Text only version of this article


Table of Contents


RT (Request Tracker) can save attachments. It saves these in the database(mysql). I had an issue where my mysqldump used in backupping RT would fail dueto an mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' byteswhen dumping table Attachments at row: xxxxx error. Adding the--max_allowed_packet=500M parameter to the mysqldump command didn't work, sohere is how to search and find big attachments in RT using mysql. This is testedon RT 4.0.8, but the RT install has been running and updated since RT 3.

If you like this article, consider sponsoring me by trying out a Digital OceanVPS. With this link you'll get $100 credit for 60 days). (referral link)

Connect to MySQL

If you have your database on the same box as RT, connect using the followingcommand, where you replace rt_db_u with the RT MySQL username:

mysql -u rt_db_u -p

It will now ask for the RT MySQL password, which you can find in the RTSiteConfig.pm file. Enter it and press ENTER.

If you have your MySQL running on a dedicated database server, connect to itremotely via the following command, again replacing rt_db_u with your databasename and database_blade_043 with your database servers hostname/IP:

mysql -u rt_db_u -h database_blade_034 -p

Select the RT database

Select the RT database with the following MySQL command, replacing rt_db withthe name of your RT MySQL database:

mysql> use rt

Finding the big attachments

mysql> SELECT DISTINCT Transactions.ObjectId,Attachments.Subject FROM Attachments LEFT OUTER JOIN Transactions ON Transactions.Id = Attachments.TransactionId WHERE Transactions.ObjectType = 'RT::Ticket' AND LENGTH(Attachments.Content) > 4000000;

This command will display all the ticket ID's and Subjects from items where theattachment size is more than 4 MB (4194304 Bytes). The outer join is becauseticket ID's are1not Attachments ID's2. It might take a while, thequery took about 4 minutes on my DB.

This was my result:

+----------+---------------------------------------------------------------+| ObjectId | Subject                                                       |+----------+---------------------------------------------------------------+|     1291 |                                                               ||     1546 |                                                               ||     1562 | [filename]                                                    ||     2016 |                                                               ||    [...] | [...]                                                         ||    85579 | [filename]                                                    |+----------+---------------------------------------------------------------+6878 rows in set (1.89 sec)

If you get the following error:

ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes

Reconnect to the MySQL database with the following parameter added to thecommand:

--max_allowed_packet=500M

More info

Now you can go to those tickets, and remove the attachments.

If you don't want to get blank Subjects, you can execute the following query:

mysql> SELECT DISTINCT Transactions.ObjectId,Attachments.Subject FROM Attachments LEFT OUTER JOIN Transactions ON Transactions.Id = Attachments.TransactionId WHERE Transactions.ObjectType = 'RT::Ticket' AND LENGTH(Attachments.Content) > 4000000 AND Attachments.Subject != "";

Notes

Do note that this is also possible via the Shredder, using the Attachmentsfilter.

Tags: helpdesk, mysql, perl, request-tracker, rt, size, tickets, tutorials