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.