Tag: phpmyadmin

Disqus Plugin Aftermath: Removing Duplicate Comments [en]

[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!

Similar Posts:

Invalid argument supplied for foreach() in wp-capabilities.php: Case Cracked! [en]

[fr] Le problème avec wp-capabilities.php qui fait qu'on peut se retrouver "exfermé" (enfermé dehors) de son blog WordPress (typiquement en cas de changement de serveur) semble avoir sa source dans le contenu du champ wp_user_roles dans la table wp_options. En particulier, pour la version française, "Abonné" est un rôle d'utilisateur, et en cas de problèmes d'encodage MySQL, le caractère accentué sera corrompu, causant ainsi l'erreur.

Il suffit de remplacer le caractère fautif dans PhpMyAdmin, et on retrouve l'accès à son blog. Bon, reste ensuite à régler les questions d'encodage... mais c'est déjà ça!

Finally. At last. Endlich. Enfin.

Once more, while trying to transfer a WordPress installation from one server to another, I found myself facing the dreaded problem which locks me out of my WordPress install with a rather cryptic message:

Warning: Invalid argument supplied for foreach() in /home/user/wp/wp-includes/capabilities.php on line 31

(Your lineage may vary.)

What happens is that WordPress cannot read user roles, and therefore, even though your password is accepted, you get a message telling you that you’re not welcome in the wp-admin section:

Vous n’avez pas les droits suffisants pour accéder à cette page.

Or, in English:

You do not have sufficient permissions to access this page.

A quick search on the WordPress forums told me that I was [not alone in my fight with wp-capabilities.php](http://wordpress.org/search/wp-capabilities.php?forums=1), but that many problems had not been resolved, and more importantly, that suggested solutions often did not work for everyone.

I’ve bumped into this problem a couple of times before, and I knew that it was linked to encoding problems in the database. (I’ve had my share of encoding problems: [once](http://climbtothestars.org/archives/2004/07/18/converting-mysql-database-contents-to-utf-8/), [twice](http://climbtothestars.org/archives/2005/02/19/problemes-dencodage-mysql/), [thrice](http://climbtothestars.org/archives/2006/07/12/finally-out-of-mysql-encoding-hell/) — “once” being on of the most-visited posts on this blog, by the way, proof if needed that [I’m not alone with mysql encoding issues](http://wordpress.org/search/mysql+encoding?forums=1) either.)

I’ll leave the detailed resolution of how to avoid/cure the MySQL problems later (adding
mysql_query("SET NAMES 'utf8'"); to wp-db.php as detailed in [this thread](http://wordpress.org/support/topic/55282?replies=7#post-311649), and as [zedrdave](http://unknowngenius.com/blog/) had already previously told me to do — should have listened! — should prevent them). So anyway, adding that line to my *working* WordPress install showed me that the problem was not so much in the database dumping process than in the way WordPress itself interacted with the database, because the dreaded wp-capabilities.php problem suddenly appeared on the original blog.

Now, this is where I got lucky. Browsing quickly through the first dozen or so of [forum threads about wp-capability.php problems](http://wordpress.org/search/wp-capabilities.php?forums=1), [this response](http://wordpress.org/support/topic/67796?replies=18#post-392812) caught my eye. It indicated that the source of the problem was the content of the wp_user_roles field (your prefix may vary). In this case, it had been split on more than one line.

I headed for the database, looked at the field, and didn’t see anything abnormal about it at first. All on one line, no weird characters… just before giving up, I moved the horizontal scrollbar to the end of the line, and there — **Eurêka!** I saw it.

Abonné

“Contributor”, in French, is “abonné”, with an accent. Accent which got horribly mangled by the MySQL problems which I’ll strive to resolve shorty. Mangled character which caused the foreach() loop to break in wp-capabilities.php, which caused the capabilities to not be loaded, which caused me to be locked out of my blog.

So, in summary: if you’re locked out of your blog and get a warning/error about wp-capabilities and some invalid foreach() loop thingy, head for PhpMyAdmin, and look carefully through the wp_user_roles field in the wp_options table. If it’s split over two or more lines, or contains funky characters, you have probably found the source of your problem.

Good luck!

Similar Posts: