WordPress Trackback Maintenance

Sometimes you just need to get rid of all that trackback data from your WordPress database. Here’s how to do it manually from your MySQL command line:

DELETE FROM wp_comments WHERE comment_type='trackback';
DELETE FROM wp_comments WHERE comment_type='pingback';

Great, but now your comments counts are all out of whack, since that integer is stored in the wp_posts table. Here’s how to check that:

SELECT wpp.id, wpp.post_title, wpp.comment_count, wpc.cnt
FROM wp_posts wpp
LEFT JOIN (
	SELECT comment_post_id AS c_post_id, count(*) AS cnt FROM wp_comments
	WHERE comment_approved = 1 GROUP BY comment_post_id) wpc
	ON wpp.id=wpc.c_post_id
	WHERE wpp.post_type IN ('post', 'page')
	AND (wpp.comment_count!=wpc.cnt OR (wpp.comment_count != 0 AND wpc.cnt IS NULL)
);

And here’s how to fix it (make a backup of your database first, just in case):

UPDATE wp_posts wpp
LEFT JOIN (
	SELECT comment_post_id AS c_post_id, count(*) AS cnt FROM wp_comments
	WHERE comment_approved = 1 GROUP BY comment_post_id) wpc
	ON wpp.id=wpc.c_post_id
	SET wpp.comment_count=wpc.cnt
	WHERE wpp.post_type IN ('post', 'page')
	AND (wpp.comment_count!=wpc.cnt OR (wpp.comment_count != 0 AND wpc.cnt IS NULL)
);

Cheers!