Deleting Comments from Tickets in Trac

Spammers apparently love Trac. After trying to fight spam tickets and later installing the SpamFilter plugin, I’ve managed to control spam tickets in the Open Yahtzee Trac site. But now spammers have started spamming the ticket comments. The bad news is that Trac (at least in version 0.11) doesn’t have built-in facilities to completely remove ticket comments.

The solution is to delete them directly from Trac’s SQLite backend. There are several types of comments: plain ones and comments that describe a change to the ticket’s properties. I’ll deal only with the plain comments, but the method I describe should also be applicable to the other kinds.

First, note down the ticket number and the comment number. The comment number appears in the URL of the permalink to it. It is the number pointed to by an arrow in the screenshot above; it will be displayed when you hover over the circled link. Now in the shell, cd to the db directory of your Trac installation and do:

sqlite3 trac.db "delete from ticket_change where ticket=<TICKET NUM> and field='comment' and oldvalue=<COMMENT NUM>"

For example, for the ticket in the screenshot:

sqlite3 trac.db "delete from ticket_change where ticket=12 and field='comment' and oldvalue=3"

You can delete different kinds of ticket changes by using other values in the field column, such as keywords and summary. Another useful column is the author column.