WordPress database error Illegal mix of collations

After successfully corrected the error caused by the WordPress plugin called Pagination which I blogged at my post, WordPress database error You have an error in your SQL syntax, I again discovered another error encountered by the blog as logged in the error_log file. The error message says:

WordPress database error Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation ‘replace’ for query  SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1 AND ((((LENGTH(wp_posts.post_content) – LENGTH(REPLACE(LCASE(wp_posts.post_content), ‘韩国性爱电影’, ”)))/18) + ((LENGTH(wp_posts.post_title) – LENGTH(REPLACE(LCASE(wp_posts.post_title), ‘韩国性爱电影’, ”)))*3/18) + ((LENGTH(wp_posts.post_content) – LENGTH(REPLACE(LCASE(wp_posts.post_content), ‘video’, ”)))/5) + ((LENGTH(wp_posts.post_title) – LENGTH(REPLACE(LCASE(wp_posts.post_title), ‘video’, ”)))*3/5) + 0)>0) AND wp_posts.post_type != ‘revision’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_author = 1 AND wp_posts.post_status = ‘private’)  ORDER BY  ((LENGTH(wp_posts.post_content) – LENGTH(REPLACE(LCASE(wp_posts.post_content), ‘韩国性爱电影’, ”)))/18) + ((LENGTH(wp_posts.post_title) – LENGTH(REPLACE(LCASE(wp_posts.post_title), ‘韩国性爱电影’, ”)))*3/18) + ((LENGTH(wp_posts.post_content) – LENGTH(REPLACE(LCASE(wp_posts.post_content), ‘video’, ”)))/5) + ((LENGTH(wp_posts.post_title) – LENGTH(REPLACE(LCASE(wp_posts.post_title), ‘video’, ”)))*3/5) + 0 DESC, POST_DATE DESC  LIMIT 670, 10 made by require, wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts

The error message is already clear… there was an illegal mix of collations between latin1 and the utf8. With this, my instinct told me that there might be some tables in the database used by my blog that are using the latin1 collation or charset while there are others in utf8.

And it might be true because this blog started to exist on March 2006. And the WordPress software at that time was using the latin1 charset. Then there was a time that the utf8 collation has been made available and became the default collation of the WordPress.

So, I logged in to my cPanel, opened the MyPHPAdmin script and checked the tables of the database used by this blog. And yes, it’s true. The tables used by this blog are still using the Latin1 collation which in conflict to the WordPress Collation default setting which is already in Utf8.

Now, I am sure that there is a need for me to convert the tables from using the Latin1 to Utf8.

Here’s what I did:

  1. Through the PHPMyAdmin Script, I downloaded the affected tables in the form of database dumps.
  2. I opened the downloaded database dumps file using the Windows Wordpad.
  3. I replaced all the CHARSET=Latin1 with CHARSET=utf8 using the Find and Replace feature of the Windows Wordpad.
  4. Then I saved the file.
  5. Using the MyPHPAdmin script, I dropped all the affected tables.
  6. Then I imported the downloaded database dumps into the database.

That’s it.

I checked the tables of that database and yes, the collations error was already corrected.

Leave a Reply

Your email address will not be published. Required fields are marked *