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!