Fixing WordPress Database Illegal Mix of Collation Error

I’ve been trying to fix the database error of the sites hosted by my VPS account and none of the steps I made is successful. This error is making every account’s error_log bigger everyday which resulted to server crashes.

One of the error says:

WordPress database error Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘like’ for query SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE ‘%Princess%’) OR (wp_posts.post_content LIKE ‘%Princess%’)) AND ((wp_posts.post_title LIKE ‘%Velasco%’) OR (wp_posts.post_content LIKE ‘%Velasco%’)) AND ((wp_posts.post_title LIKE ‘%â��%’) OR (wp_posts.post_content LIKE ‘%â��%’)) AND ((wp_posts.post_title LIKE ‘%Lonely%’) OR (wp_posts.post_content LIKE ‘%Lonely%’)) AND ((wp_posts.post_title LIKE ‘%Night%’) OR (wp_posts.post_content LIKE ‘%Night%’)) AND ((wp_posts.post_title LIKE ‘%mediafire%’) OR (wp_posts.post_content LIKE ‘%mediafire%’))) AND (wp_posts.post_password = ”) AND wp_posts.post_type IN (‘post’, ‘page’, ‘attachment’) AND (wp_posts.post_status = ‘publish’) ORDER BY wp_posts.post_date DESC LIMIT 0, 10 made by require(‘wp-blog-header.php’), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts

There are several variations of the statement the main point is that there has been an illegal mix of collations between latin1.shwedish_ci and utf8_general_ci.

After examining the databases, I learned these things:

  1. The default collation on sql server is latin1_swedish while the existing database tables are using the utf8 collation which is based on my understanding is the default collation used by WordPress.

Leave a Reply

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