Raymii.org
Quis custodiet ipsos custodes?Home | About | All pages | Cluster Status | RSS Feed
RT (Request Tracker) - find and delete big attachments
Published: 17-01-2013 | Author: Remy van Elst | Text only version of this article
❗ This post is over eleven years old. It may no longer be up to date. Opinions may have changed.
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 due
to an mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes
when dumping table Attachments at row: xxxxx error. Adding the
--max_allowed_packet=500M parameter to the mysqldump command didn't work, so
here is how to search and find big attachments in RT using mysql. This is tested
on RT 4.0.8, but the RT install has been running and updated since RT 3.
Recently I removed all Google Ads from this site due to their invasive tracking, as well as Google Analytics. Please, if you found this content useful, consider a small donation using any of the options below:
I'm developing an open source monitoring app called Leaf Node Monitoring, for windows, linux & android. Go check it out!
Consider sponsoring me on Github. It means the world to me if you show your appreciation and you'll help pay the server costs.
You can also sponsor me by getting a Digital Ocean VPS. With this referral link you'll get $100 credit for 60 days.
Connect to MySQL
If you have your database on the same box as RT, connect using the following
command, 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 RT
SiteConfig.pm file. Enter it and press ENTER.
If you have your MySQL running on a dedicated database server, connect to it
remotely via the following command, again replacing rt_db_u with your database
name 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 with
the 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 the attachment size is more than 4 MB (4194304 Bytes). The outer join is because ticket ID's are1 not Attachments ID's2. It might take a while, the query 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 the command:
--max_allowed_packet=500M
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 Attachments filter.
Tags: helpdesk , mysql , perl , request-tracker , rt , size , tickets , tutorials