HOWTO: Regenerate Gravatars in WordPress

A troublesome plugin recently corrupted some of the avatars on my blog's comments. This is a quick HOWTO for regenerating them.

Gravatars are based on the MD5 hash of a user's email. For some reason, the plugin had overwritten the avatar field with the text http://identicon

This MySQL query finds all the comment IDs which have that dodgy text:

 SQLSELECT 
   comment_id FROM `wp_commentmeta` 
   WHERE `meta_key` LIKE 'avatar' 
   AND `meta_value` LIKE 'http://identicon'

Using a SubQuery we can find all the email addresses for those comments - and generate an MD5 for them:

 SQLSELECT 
   comment_author_email, 
   MD5(comment_author_email) AS md5 
   FROM `wp_comments` 
   WHERE comment_id IN
      (SELECT 
         comment_id FROM `wp_commentmeta` 
         WHERE `meta_key` LIKE 'avatar' 
         AND `meta_value` LIKE 'http://identicon')

A good first step, but how to get all those MD5s into URls and then back into the database?

First, they need to be concatanated.

 SQLSELECT 
   comment_id, 
   CONCAT("https://www.gravatar.com/avatar/", MD5(comment_author_email)) AS gravatar
   FROM `wp_comments` 
   WHERE comment_id IN
      (SELECT 
         comment_id FROM `wp_commentmeta` 
         WHERE `meta_key` LIKE 'avatar' 
         AND `meta_value` LIKE 'http://identicon')

Let's put that all together in one query:

 SQLUPDATE wp_commentmeta
   JOIN wp_comments
   ON wp_commentmeta.comment_id = wp_comments.comment_ID
   SET meta_value = CONCAT("https://www.gravatar.com/avatar/", MD5(comment_author_email))
   WHERE meta_key LIKE 'avatar'
   AND meta_value LIKE "http://identicon"

Run that, and all your broken Gravatars will be regenerated.

Thanks to Ed Jefferson for his help with this!