I recently ran into trouble with a Wordpress installation of mine, where a homemade script went totally overboard, and never stopped running, adding posts to the database. Over a few days it added close to 93.000 posts, of which all were duplicates.
After a bit of searching I found the problem, but getting into the database to clean up was a bit of a problem, since the script was still running. I had to remove the script totally from my install in order to stop it!
I was now left with 96.000 something posts, of which I knew 93.000 or so were duplicates! How to clean those up? I could use the manual approach, which I started on, but I soon realised I would be old and grey before that fixed the problem.
A bit of Google searching (where else to start your research?) I found a nifty script to help with exactly this problem:
DELETE bad_rows.*
from wp_posts as bad_rows
inner join (
select post_title, MIN(id) as min_id
from wp_posts
group by post_title
having count(*) > 1
) as good_rows on good_rows.post_title = bad_rows.post_title
and good_rows.min_id <> bad_rows.id
This solution was found on the Wordpress.org support forum
This solved the problem, and my blog is now acting normally again.
P.s. remember to back up your database before doing this!
