Technical Errata

A mysql upgrade scrambled quote characters across this blog. This technical post explains the fix. Warning -- off-topic post follows! Not a marketing post.
If you're still reading at this point, perhaps you found this post via search and perhaps you're trying to fix scrambled quote characters in your blog. Welcome. When we upgraded our mysql database powering this blog last week, the open and close single and double quote characters turned to mush. At first we though the issue was in the WordPress level. After discovering the problem was in the database, we found a helpful post by Derek Sivers over at ORielly's OnLamp (Turning MySQL data in latin1 to utf8 utf-8) which provided the solution. First, make sure your problem is in your database, not in your WordPress. Second, confirm these hex encodings are correct for your situation by following Derek's advice. And third, back up your database before issuing these updates as they're irreversible. These queries worked for us:
  /* fix open double quote  */  update wp_posts set post_content=replace(post_content,  unhex('C3A2E282ACC593'), '"')  where post_content regexp unhex('C3A2E282ACC593'); /* fix close double quote  */  update wp_posts set post_content=replace(post_content,  unhex('C3A2E282ACC29D'), '"')  where post_content regexp unhex('C3A2E282ACC29D'); /* fix open single quote  */  update wp_posts set post_content=replace(post_content,  unhex('C3A2E282ACCB9C'), '\'')  where post_content regexp unhex('C3A2E282ACCB9C'); /* fix close single quote */  update wp_posts set post_content=replace(post_content,  unhex('C3A2E282ACE284A2'), '\'')  where post_content regexp unhex('C3A2E282ACE284A2');  

The query stream we used to find Derek's post:

  • "wordpress quotes"
  • "wordpress quotes smart"
  • "strange quote character wordpress"
  • "wordpress set bloginfo charset"
  • "post charset wordpress"
  • "wordpress charset"
  • "malfunctioning quotes wordpress"
  • "wptexturize"
  • "wordpress character quotes in database"
  • "mysql smart quotes"
  • "mysql utf upgrade"
  • "mysql quote characters upgrade"


Join the Discussion