Deleting tickets in bulk for GFI HelpDesk
The topic suggests a solution to delete the tickets in bulk, without causing the database to timeout.
If you try to delete a significant amount of tickets, empty trash with thousands of records or cleanup spam you suddenly got attacked with, your database may probably get timed-out. Instead, you can use the following solution:
- Update the following PHP Script with applicable Database Host, Database Name, Database Username and Database Password for the concerned MySQL Database:
- Backup your database. Make sure you have a valid and latest backup of the database before implementing the solution.
- Execute the script.
$databasehost = "";
$databasename = "";
$databaseusername = "";
$databasepassword = "";
$foundAny = 0;
$con = @mysql_connect($databasehost, $databaseusername, $databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());
$result = mysql_query("SELECT ticketid FROM swtickets WHERE date(from_unixtime(dateline)) < '2011-12-31'");
echo "Starting cleanup Process<br />";
while ($row = mysql_fetch_array($result)) {
$foundAny = 1;
$delete = mysql_query("DELETE FROM swticketpostlocks WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketpostlocks Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketlocks WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketlocks Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketlinkchains WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketlinkchains Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketfollowups WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketfollowups Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketdrafts WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketdrafts Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swattachments WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swattachments Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swescalationpaths WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swescalationpaths Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketnotes WHERE linktypeid = " . $row['ticketid']);
//echo "Deleting: swticketnotes Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketauditlogs WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketauditlogs Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketlinkedtables WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketlinkedtables Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketlinks WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketlinks Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketmergelog WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketmergelog Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketmessageids WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketmessageids Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketnotes WHERE linktypeid = " . $row['ticketid']);
//echo "Deleting: swticketnotes Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketposts WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketposts Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketrecipients WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketrecipients Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swticketwatchers WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swticketwatchers Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swcustomfieldvalues WHERE typeid = " . $row['ticketid']);
//echo "Deleting: swcustomfieldvalues Result:" . print_r ($delete). "<br />";
$result2 = mysql_query("SELECT tickettimetrackid FROM swtickettimetracks WHERE ticketid = " . $row['ticketid']);
while ($row2 = mysql_fetch_array($result2)) {
$delete = mysql_query("DELETE FROM swtickettimetracknotes WHERE tickettimetrackid = " . $row2['tickettimetrackid']);
//echo "Deleting: swtickettimetracknotes Result:" . print_r ($delete). "<br />";
}
$delete = mysql_query("DELETE FROM swtickettimetracks WHERE ticketid = " . $row['ticketid']);
//echo "Deleting: swtickettimetracks Result:" . print_r ($delete). "<br />";
$delete = mysql_query("DELETE FROM swtickets WHERE ticketid = " . $row['ticketid']);
echo "Deleting: swtickets -" . $row['ticketid'] . " Result:" . print_r($delete) . "<br />";
}
@mysql_close($con);
if ($foundAny == 0) {
echo "No tickets where found within that date range";
}
else {
echo "Ticket Cleanup Complete.";
}
?>
You can edit the following line to match your needs (it currently removes tickets that were created before a certain date):
For example, if you need to delete all tickets from one user:
If you need to remove all tickets from trash: