Mysql trick to delete duplicate Wordpress posts

I recently ran into distress with a Wordpress installation of mine, where a homemade script went perfectly overboard, and never stopped running, count posts to the database. Over a few days it added close to 93.000 posts, of which all were duplicates.

With a bit of searching I found the conundrum, but getting into the database to clean up was a bit of a conundrum, since the script was still running. I had to remove the script perfectly 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 deal with, which I started on, but I soon realised I would be ancient and grey before that fixed the conundrum.

A bit of Google searching (where else to start your research?) I found a nifty script to help with exactly this conundrum:

DELETE bad_rows.*
from wp_posts as bad_rows
inside 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 conundrum, and my blog is now performing arts normally again.

P.s. remember to back up your database before doing this!

Tagged as: "group by" in wordpress mysql, "if is duplicate delete all less 1 mysql", blog, clean up duplicates in mysql, code, delete all duplicate posts wordpress, delete all posts mysql wordpress, delete duplicate comment in wordpress, delete duplicate post, delete duplicate post wordpress, delete duplicate posts, delete duplicate posts in mysql, delete duplicate posts plugin wordpress, delete duplicated posts in wordpress, delete duplicated posts wordpress, delete duplicates emails mysql, delete group by mysql, delete mysql same table join, delete wordpress duplicate posts wp_posts mysql, duplicate posts wordpress, duplicate wordpress installation, forum, help, how to delete duplicate post wordpress, how to delete wordpress post via mysql, mysql, mysql clean duplicate, mysql cleaning database, mysql cleaning up duplicates, mysql delete blog post, mysql delete group by, mysql found duplicate now delete, mysql how to delete duplicate, mysql how to delete duplicates, mysql how to remove duplicates, mysql inner join duplicate, mysql join remove duplicates, mysql posts delete wordpress, mysql remove dups, mysql remove recent duplicate, mysql tool delete duplicate, mysql trick, phpmyadmin, remove duplicat posts wordpress, remove duplicate posts wordpress, remove duplicate wordpress, remove duplicates in wordpress, remove proudly wordpress, simple way to delete duplicates mysql, tip, tool for cleaning database wordpress, trick, tutorialvine duplicate posts, wordpress, wordpress delete duplicate comments, wordpress delete duplicate post, wordpress delete duplicate posts, wordpress delete old posts, wordpress dupe remover mysql, wordpress duplicate post delete, wordpress duplicate posts, wordpress duplicate posts in db, wordpress duplicate posts not found, wordpress how to delete all posts in category, wordpress manual delete posts, wordpress mysql inactive plugins remove, wordpress post views mysql, wordpress remove categories mysql, wordpress remove duplicate posts, wordpress search database for duplicate comments, wordpress tag delete mysql

  • wordpress remove duplicate posts
  • wordpress delete duplicate posts
  • computers internet blog
  • remove duplicate posts wordpress
  • wordpress delete duplicate mysql
  • clean up duplicates in mysql
  • a mysql script to delete duplicates
  • delete all posts mysql wordpress
  • find/delete duplicate wordpress posts
  • remove duplicate posts from wordpress

{ 5 comments }

sagbee 07/11/2008 at 14:34

eh ? you must have to make plugins for simple user who dont know how to treat their mysql while running that script… so far i like it.. since i was looking for same business too ;)

Cheer

admin 10/11/2008 at 17:48

Hi sagbee

I reckon you’re aptly. I should make a plugin for this. Let me reckon a small on how to do it, and I’ll get on it.

Excellent thought by the way :-)

Sushubh 07/12/2008 at 08:08

i installed some comment plugin and it made duplicates of all the comments! if you go ahead and make a plug-in add a feature to remove all duplicate comments too! :P

Jeremy 08/12/2008 at 04:02

Thanks for that fantastic script. That saved my site. Not only was my site throwing duplicate posts, it was ’screwing’ with my page format. Just a note for those not familiar with running mysql scripts, make sure to change the wp_posts to no matter what your table is for posts (i.e. mine was set to wp_5ykyp9_posts per my hosting company.) I adjusted to script to reflect that table and it worked perfectly. Thanks again!

Timewarp 02/06/2009 at 16:39

This worked fantastic thanks, deleted a few thousand duplicates!

Now Im wondering if here is a way to use htaccess or something to redirect the dead duplicate URLs to the original URLs? I question because many of the duplicates were indexed.

Any suggestions?

Thanks

Comments on this entry are closed.

Previous post:

Next post: