- January 4, 2007
- 1 comment
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”
If you like this post, consider subscribing to our RSS feed. You can also have new posts sent to you via email.
Similar Posts
- Wordpress Permission Hack
- WordPress As CMS
- Internal Blogs: Less Email, Blogging Training Wheels, Link-sharing, BizCulture
- Tips For Using WordPress As A Content Management System (CMS)
- More Tips For Using WordPress As A Web Content Management System
Thanks - I found I had a few more to do on one web, some more variations on quotes, dashes etc as the post title’s and names. I have put the SQL code on my web if anyone needs more. See http://www.anmari.com/anmari/2007/04/strange-characters/