Database Error Illegal Mix of Collations
I have gotten this error twice now...
I did a search of the database for the word "Levitra", because it is the unique word in the code, and only arrived at one entry in wp_options > option_name "disallowed_keys". This word is one of over 40,000 disallowed keys. I have searched the entire database and all the posts and comments on the site and have found nothing. I scoured the current theme and the default theme and have found nothing. I checked the theme's scripts and any other scripts and found nothing. My security plugin (Sucuri) finds nothing - No malicious JavaScript, No malicious iFrames, No suspicious redirections, No blackhat SEO spam, No anomaly detection. I am at a loss here. Any suggestions would be appreciated.
WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation 'like' for query SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships AS tr ON (wp_posts.ID = tr.object_id) LEFT JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) LEFT JOIN wp_terms AS t ON (tt.term_id = t.term_id) WHERE 1=1 AND ( ((wp_posts.post_title LIKE '%%') OR (wp_posts.post_content LIKE '%%') OR (wp_posts.post_excerpt LIKE '%%') OR ( ( t.name LIKE '%%' ) )) AND ((wp_posts.post_title LIKE '%Pills%') OR (wp_posts.post_content LIKE '%Pills%') OR (wp_posts.post_excerpt LIKE '%Pills%') OR ( ( t.name LIKE '%Pills%' ) )) AND ((wp_posts.post_title LIKE '%without%') OR (wp_posts.post_content LIKE '%without%') OR (wp_posts.post_excerpt LIKE '%without%') OR ( ( t.name LIKE '%without%' ) )) AND ((wp_posts.post_title LIKE '%prescription%') OR (wp_posts.post_content LIKE '%prescription%') OR (wp_posts.post_excerpt LIKE '%prescription%') OR ( ( t.name LIKE '%prescription%' ) )) AND ((wp_posts.post_title LIKE '%on%') OR (wp_posts.post_content LIKE '%on%') OR (wp_posts.post_excerpt LIKE '%on%') OR ( ( t.name LIKE '%on%' ) )) AND ((wp_posts.post_title LIKE '%%') OR (wp_posts.post_content LIKE '%%') OR (wp_posts.post_excerpt LIKE '%%') OR ( ( t.name LIKE '%%' ) )) AND ((wp_posts.post_title LIKE '%www.NetDoctor.store%') OR (wp_posts.post_content LIKE '%www.NetDoctor.store%') OR (wp_posts.post_excerpt LIKE '%www.NetDoctor.store%') OR ( ( t.name LIKE '%www.NetDoctor.store%' ) )) AND ((wp_posts.post_title LIKE '%%') OR (wp_posts.post_content LIKE '%%') OR (wp_posts.post_excerpt LIKE '%%') OR ( ( t.name LIKE '%%' ) )) AND ((wp_posts.post_title LIKE '%Buy%') OR (wp_posts.post_content LIKE '%Buy%') OR (wp_posts.post_excerpt LIKE '%Buy%') OR ( ( t.name LIKE '%Buy%' ) )) AND ((wp_posts.post_title LIKE '%Levitra%') OR (wp_posts.post_content LIKE '%Levitra%') OR (wp_posts.post_excerpt LIKE '%Levitra%') OR ( ( t.name LIKE '%Levitra%' ) )) AND ((wp_posts.post_title LIKE '%from%') OR (wp_posts.post_content LIKE '%from%') OR (wp_posts.post_excerpt LIKE '%from%') OR ( ( t.name LIKE '%from%' ) )) AND ((wp_posts.post_title LIKE '%$0.84/pill%') OR (wp_posts.post_content LIKE '%$0.84/pill%') OR (wp_posts.post_excerpt LIKE '%$0.84/pill%') OR ( ( t.name LIKE '%$0.84/pill%' ) )) AND ((wp_posts.post_title LIKE '%Purchase%') OR (wp_posts.post_content LIKE '%Purchase%') OR (wp_posts.post_excerpt LIKE '%Purchase%') OR ( ( t.name LIKE '%Purchase%' ) )) AND ((wp_posts.post_title LIKE '%Levitra%') OR (wp_posts.post_content LIKE '%Levitra%') OR (wp_posts.post_excerpt LIKE '%Levitra%') OR ( ( t.name LIKE '%Levitra%' ) )) AND ((wp_posts.post_title LIKE '%Canada%') OR (wp_posts.post_content LIKE '%Canada%') OR (wp_posts.post_excerpt LIKE '%Canada%') OR ( ( t.name LIKE '%Canada%' ) )) AND ((wp_posts.post_title LIKE '%-%') OR (wp_posts.post_content LIKE '%-%') OR (wp_posts.post_excerpt LIKE '%-%') OR ( ( t.name LIKE '%-%' ) )) AND ((wp_posts.post_title LIKE '%Levitra%') OR (wp_posts.post_content LIKE '%Levitra%') OR (wp_posts.post_excerpt LIKE '%Levitra%') OR ( ( t.name LIKE '%Levitra%' ) )) AND ((wp_posts.post_title LIKE '%20%') OR (wp_posts.post_content LIKE '%20%') OR (wp_posts.post_excerpt LIKE '%20%') OR ( ( t.name LIKE '%20%' ) )) AND ((wp_posts.post_title LIKE '%Mg%') OR (wp_posts.post_content LIKE '%Mg%') OR (wp_posts.post_excerpt LIKE '%Mg%') OR ( ( t.name LIKE '%Mg%' ) )) AND ((wp_posts.post_title LIKE '%Order%') OR (wp_posts.post_content LIKE '%Order%') OR (wp_posts.post_excerpt LIKE '%Order%') OR ( ( t.name LIKE '%Order%' ) )) AND ((wp_posts.post_title LIKE '%Online%') OR (wp_posts.post_content LIKE '%Online%') OR (wp_posts.post_excerpt LIKE '%Online%') OR ( ( t.name LIKE '%Online%' ) ))) AND wp_posts.post_type IN ('post', 'page') AND (wp_posts.post_status = 'publish') ORDER BY (CASE WHEN wp_posts.post_title LIKE '% Pills without prescription on www.NetDoctor.store Buy Levitra from $0.84/pill Purchase Levitra Canada - Levitra 20 Mg Order Online%' THEN 2 ELSE 6 END), wp_posts.post_date DESC LIMIT 0, 20 made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_postsI did a search of the database for the word "Levitra", because it is the unique word in the code, and only arrived at one entry in wp_options > option_name "disallowed_keys". This word is one of over 40,000 disallowed keys. I have searched the entire database and all the posts and comments on the site and have found nothing. I scoured the current theme and the default theme and have found nothing. I checked the theme's scripts and any other scripts and found nothing. My security plugin (Sucuri) finds nothing - No malicious JavaScript, No malicious iFrames, No suspicious redirections, No blackhat SEO spam, No anomaly detection. I am at a loss here. Any suggestions would be appreciated.
-
Based on the error it sounds like you have tables with different Collation that MySQL does not like. If you go into phpMyAdmin and select the database the "main" part of the screen will show you all the tables along with the row count, type and Collation (along with other tings). Based on what you have posted, I'm assuming you'll see some tables with utf8_general_ci for the Collation and others with utf8mb4_unicode_520_ci. That will just identify what the issue is. Converting them to be one consistent Collation is going to be more complex. The Character Set and Collation tell MySQL how to store the various characters and how to sort them. Hopefully that gets you in the right direction. 0 -
Thank you, ffeingol, I will check out your suggestion and post back here. 0 -
The bulk of the tables are MyISAM and utf8_general_c1 but, there are several tables that are InnoDB and use utf8mb4_unicode_520_ci. These tables are plugins that have been on the site for a long time. They are WP-Optimize, Yoast, and Code Snippets. The first two have been on the site since it was built. All three were updated over a month ago and the error only started yesterday. So, I do not think this is a unicode issue; it seems like an issue with something inserted into the site somehow and I cannot find it. What concerns me is that the code I posted looks like it is selling "Levitra" pills and I do not know how this got inserted into the site or where in the site it is. 0 -
So... this is a PharmaHack. (google it) How it got past my Sucuri Security Plugin, I do not know. I'm working on getting it out. This thread is resolved. 0
Please sign in to leave a comment.
Comments
4 comments