Month: September 2013

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:

[code language=”sql”]DELETE FROM wp_comments WHERE comment_type=’trackback’;
DELETE FROM wp_comments WHERE comment_type=’pingback’;[/code]

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:

[code language=”sql”]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)
);[/code]

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

[code language=”sql”]
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)
);[/code]

Cheers!