Free Cleaning esupport database

[Antonio]

Maybe this stuff will be handy.

/* attachments from tickets with last post > then 3 months */
DELETE FROM swattachments WHERE ticketid IN (
	SELECT ticketid
	FROM (
		SELECT
			t.ticketid
			,MAX(tp.dateline) AS dateline
		FROM swtickets t INNER JOIN swticketposts tp ON t.ticketid=tp.ticketid
		GROUP BY t.ticketid
	) g
	WHERE g.dateline < (UNIX_TIMESTAMP()-7776000)
)

/* now get rid of "hanging" rows in miscelaneous tables */

/* attachments */
DELETE FROM swattachments WHERE ticketid NOT IN (SELECT ticketid FROM swtickets)

/* attachments chunks */
DELETE FROM swattachmentchunks WHERE attachmentid NOT IN (SELECT attachmentid FROM swattachments)

/* tickets posts */
DELETE FROM swticketposts WHERE ticketid NOT IN (SELECT ticketid FROM swtickets)

/* tickets locks */
DELETE FROM swticketlocks WHERE ticketid NOT IN (SELECT ticketid FROM swtickets)

/* ticket message ids */
DELETE FROM swticketmessageids WHERE ticketpostid NOT IN (SELECT ticketpostid FROM swticketposts)

/* ticket posts index */
DELETE FROM swticketpostindex WHERE ticketpostid NOT IN (SELECT ticketpostid FROM swticketposts)

/* ticket words */
DELETE FROM swticketwords WHERE ticketwordid NOT IN (SELECT ticketwordid FROM swticketpostindex)

/* clear parser log */
delete from swparserlogs
delete from swparserlogdata

Let's see Kayako developers comments.

[dogslife]

Thanks Antonio!

Is this the only way to delete attachments?

[supportskins]

great post!

[mopa]

Did something change since this was written?

I get this error in myphpadmin:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM swattachments WHERE ticketid NOT IN (SELECT ticketid FROM swtickets)' at line 16

[Dynadata]

You do not have the SupportSuite product and your table prefix should be different then he used.
So change every prefix in front of the table names with your own prefix.

[mopa]

I don't see that I have any other prefix.
My tables are just called swparserlogdata, swsessions etc. without any prefix_swsessions.
Could it be something else?

[nolageek]

not working for me either.

[mopa]

Funny. I have tried to run each "line" one at a time like instead of putting all the text into the sql I only run ie. this line:

DELETE FROM swattachments WHERE ticketid NOT IN (SELECT ticketid FROM swtickets)

and then afterwards I moved on with another line. It seemed to work.

[Matthew]

Isn't this now superfluous with the Tickets>Purge Ticket Attachments search criteria now available in the Admin CP?