Upgrading to Mysql5 – more strange characters

An upgrade of a wordpress site’s Database from Mysql 4 to Mysql 5 gave some surprising results – some strange characters floating around – those funny question marks. I had already successfully converted (I thought) some other sites. When I thought about what was different, I realised most of this content had come from writers who may have done their original text in Ms Word. Rule No. 1: never cut and paste direct from MsWord!

I found this useful article from Derek Sivers via Paul Hepworth’s post and managed to work out that I also had to do the following replacements in addition to those recommended by Paul. As well as the post content, I had to check the post_title and post_name fields, and replace these additional strings:

-- C3A2E282ACEFBFBD = " (another double quote)
UPDATE wp_posts SET post_content =
           REPLACE(post_content, UNHEX('C3A2E282ACEFBFBD'), "\"") 
WHERE post_content REGEXP UNHEX('C3A2E282ACEFBFBD');
UPDATE wp_posts SET post_title =
           REPLACE(post_title, UNHEX('C3A2E282ACEFBFBD'), "\"") 
           WHERE post_title REGEXP UNHEX('C3A2E282ACEFBFBD');
UPDATE wp_posts SET post_name =
           REPLACE(post_name, UNHEX('C3A2E282ACEFBFBD'),"\"") 
           WHERE post_name REGEXP UNHEX('C3A2E282ACEFBFBD');
-- C3A2E282ACCB9C = ' (an opening quote)
UPDATE wp_posts SET post_content =
           REPLACE(post_content, UNHEX('C3A2E282ACCB9C'),
           "'") WHERE post_content REGEXP UNHEX('C3A2E282ACCB9C');
UPDATE wp_posts SET post_title =
           REPLACE(post_title, UNHEX('C3A2E282ACCB9C'), "'")
           WHERE post_title REGEXP UNHEX('C3A2E282ACCB9C');
UPDATE wp_posts SET post_name =
           REPLACE(post_name, UNHEX('C3A2E282ACCB9C'), "'")
           WHERE post_name REGEXP UNHEX('C3A2E282ACCB9C');
-- C3A2E282ACE284A2 = ' (a close quote)
UPDATE wp_posts SET post_content = REPLACE(post_content, UNHEX('C3A2E282ACE284A2'), "'")
           WHERE post_content REGEXP UNHEX('C3A2E282ACE284A2');
UPDATE wp_posts SET post_title = REPLACE(post_title, UNHEX('C3A2E282ACE284A2'), "'")
           WHERE post_title REGEXP UNHEX('C3A2E282ACE284A2');
UPDATE wp_posts SET post_name  = REPLACE(post_name , UNHEX('C3A2E282ACE284A2'), "'")
           WHERE post_name  REGEXP UNHEX('C3A2E282ACE284A2');
-- C3A2E282ACE2809C = - (a dash)
UPDATE wp_posts SET post_content = REPLACE(post_content, UNHEX('C3A2E282ACE2809C'), "-")
           WHERE post_content REGEXP UNHEX('C3A2E282ACE2809C');
UPDATE wp_posts SET post_title = REPLACE(post_title, UNHEX('C3A2E282ACE2809C'), "-")
           WHERE post_title REGEXP UNHEX('C3A2E282ACE2809C');
UPDATE wp_posts SET post_name  = REPLACE(post_name , UNHEX('C3A2E282ACE2809C'), "-")
           WHERE post_name  REGEXP UNHEX('C3A2E282ACE2809C');

Backup your Database first and then you should be able to cut and paste the code above. You may need to change your table names.

Published by

anmari

the developer at anmari