[fr] Comment se débarrasser de 5000 commentaires à double dans sa base de données WordPress!
Now that Disqus integrates Friendfeed comments, I could be tempted to give it another try, if I hadn’t spent an hour yesterday cleaning up my database because of an earlier attempt to use Disqus on this blog. After the story, how I did it — in case you’re in the same mess and could use the help.
Back in August, I installed the Disqus plugin for WordPress. Things started off not too badly, though I was a bit concerned that the plugin seemed to have duplicated all the comments in my database. It didn’t seem to show up on the blog though, so I didn’t worry too much.
After a few months, I was a bit frustrated with Disqus and the plugin (which was clearly an older version than the Disqus plugin available now). Moderating comments through the WordPress interface seemed to work erraticly, and some spam just wouldn’t accept to stay in the spambox. I never really tried to identify the exact problems too closely, I have to admit, but things were not really working how I expected them to.
Then a few (unrelated) people told me they had completely failed to comment on my blog with the new system. At some point, I got fed up and uninstalled it. Unfortunately, the duplicate comments which had been hidden from view remained there after uninstalling the plugin, so all the old comments appeared on the blog twice. I let the problem sit for a long time before attempting to fix it — wild hope there might be a ready-made script out there I could just run& in vain.
Here’s how I tackled the problem this week-end and ended up removing the duplicate comments without too much trouble, through PhpMyAdmin (PMA for short).
- In PMA, I made sure that duplication seemed constant — it was
- I discovered that the duplicate comments had “DISQUS” in the user-agent field
- I dug around until I identified the last duplicate comment (when I installed the Disqus plugin, actually; I sorted the database comments table by comment date to do that)
- I did a search, selecting comments which were younger than the last duplicate comment date AND had “DISQUS” in the user-agent field (the date bit is important, because comments posted while the plugin were active have “DISQUS” in the user-agent field but are not duplicates)
- Then, I deleted everything that came up in the search — about 5000 comments (it helps to tell PMA to display 3000 lines per page when doing that :-))
Hope this can help somebody, and remember: always back up your database first!
- The Four Lost Months Are Back! [en] (2009)
- Trying the Disqus WordPress Plugin [en] (2008)
- CTTS Upgraded, Jetpacked, and Roboted [en] (2011)
- Progress in Restoring CTTS [en] (2009)
- Catching up With Backtype [en] (2010)
- Hairy .htaccess Dreamhost WordPress Problem [en] (2006)
- Converting MySQL Database Contents to UTF-8 [en] (2004)
- Invalid argument supplied for foreach() in wp-capabilities.php: Case Cracked! [en] (2007)
- Sampling the Blogosphere [en] (2006)
- Simple Technorati Tags Plugin for WordPress [en] (2005)