Anmari

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.

Related posts:

1. A wordpress playground - a second installation or blog on the same site We needed a training area for a client on his...
2. Some help and a correction for Vista installation of Apache, PHP, Mysql, PhpAdmin The next Vista step was to get my development web...