This is a only version

Found at: raymii.org:70/RT_find__and_delete_big_attachments.txt

This is a text-only version of the following page on https://raymii.org:
Title       : 	RT (Request Tracker) - find and delete big attachments
Author      : 	Remy van Elst
Date        : 	17-01-2013
URL         : 	https://raymii.org/s/tutorials/RT_find__and_delete_big_attachments.html
Format      : 	Markdown/HTML
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.

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 are[1]][2] [not Attachments ID's[2].][3] 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
[More info][4]
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
   [1]: https://www.digitalocean.com/?refcode=7435ae6b8212
   [2]: http://lists.bestpractical.com/pipermail/rt-users/2013-January/078909.html
   [3]: http://lists.bestpractical.com/pipermail/rt-users/2007-June/046395.html
   [4]: https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
All the text on this website is free as in freedom unless stated otherwise. 
This means you can use it in any way you want, you can copy it, change it 
the way you like and republish it, as long as you release the (modified) 
content under the same license to give others the same freedoms you've got 
and place my name and a link to this site with the article as source.
This site uses Google Analytics for statistics and Google Adwords for 
advertisements. You are tracked and Google knows everything about you. 
Use an adblocker like ublock-origin if you don't want it.
All the code on this website is licensed under the GNU GPL v3 license 
unless already licensed under a license which does not allows this form 
of licensing or if another license is stated on that page / in that software:
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    GNU General Public License for more details.
    You should have received a copy of the GNU General Public License
    along with this program.  If not, see .
Just to be clear, the information on this website is for meant for educational 
purposes and you use it at your own risk. I do not take responsibility if you 
screw something up. Use common sense, do not 'rm -rf /' as root for example. 
If you have any questions then do not hesitate to contact me.
See https://raymii.org/s/static/About.html for details.