Ignore the advice below, this error is still produced and tables are still in latin1 encoding, I will follow up once I’ve looked into it further
So the databases for my blogs were setup a long time ago when the mysql database used a latin1 character set and the latin1_swedish_ci collation. With the 2.1.3 release of WordPress this was changed to UTF-8 which applied to new installs. I was recently looking through my errors logs & noticed frequent errors like
"WordPress database error Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' for query SELECT comment_ID FROM wp_comments WHERE comment_post_ID = '1' AND comment_parent = '0' AND comment_approved != 'trash' AND ( comment_author = 'Someone' OR comment_author_email = 'email@example.com' ) AND comment_content = 'Some comment' LIMIT 1 made by wp_new_comment, wp_allow_comment
As these errors were always generated by spammers I had alway assumed that these errors were cause by them using misconfigured scripts/tools? after all, every time you upgrade WordPress it performs a database update if it’s required!
I suspect I may have screwed things up by replacing the
wp-config.php with a more recent copy. This blog started off on a very early zero dot release of WordPress so I was missing lots of things such as the variables for “Authentication Unique Keys” so I swapped files around.
The WordPress site has an article on converting database character sets which gives a brief history & lists several different approaches to solving this problem. I didn’t want to dump databases & restore so I took the longer way of converting tables manually, thought I was done & left it that. Earlier I drafted up a blog post on Acme and happened to scroll down the front page by accident where I noticed that one of my blog posts was incomplete, looking through the archive showed other posts which also had content missing.
Luckily I’d taken a backup before starting the conversion process.
I created a new database for each blog & re-imported each dump using
mysql -u root -p --default-character-set=utf8 mydbname <; mydbbackup.sql
as instructed in the WordPress article
Revisiting the site showed the incomplete posts were fixed.