“WordPress database error Illegal mix of collations”

[Living Miracles]

Hello,

Within the past week, we’ve started to notice across at least two sites so far that “WordPress database error Illegal mix of collations” entries are quickly filling up our PHP Error logs. It seems like some sort of spam attack. The following are a couple of examples of these entries in the log:

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 DB-PREFIX_posts.ID FROM DB-PREFIX_posts WHERE 1=1 AND (((DB-PREFIX_posts.post_title LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%') OR (DB-PREFIX_posts.post_excerpt LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%') OR (DB-PREFIX_posts.post_content LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%'))) AND (DB-PREFIX_posts.post_password = '') AND DB-PREFIX_posts.post_type IN ('post', 'page', 'attachment') AND (DB-PREFIX_posts.post_status = 'publish') ORDER BY (CASE WHEN DB-PREFIX_posts.post_title LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%' THEN 1 WHEN DB-PREFIX_posts.post_title LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%' THEN 2 WHEN DB-PREFIX_posts.post_excerpt LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%' THEN 4 WHEN DB-PREFIX_posts.post_content LIKE '%🦎 Buy Generic Stromectol 🍀 www.PillShipment.com 🍀 Low Cost Pills 🦎Order Stromectol 6 Mg Tablet. Buy Ivermectin Stromectol%' THEN 5 ELSE 6 END), DB-PREFIX_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

WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation '=' for query SELECT DISTINCT(relevanssi.doc), relevanssi.*, relevanssi.title * 5 +
				relevanssi.content * 6 + relevanssi.comment * 0 +
				relevanssi.tag * 0.75 + relevanssi.link * 0 +
				relevanssi.author + relevanssi.category * 0.75 + relevanssi.excerpt +
				relevanssi.taxonomy + relevanssi.customfield + relevanssi.mysqlcolumn AS tf
				FROM DB-PREFIX_relevanssi AS relevanssi  WHERE  relevanssi.term = '🔻💱order'   AND relevanssi.doc NOT IN (
						SELECT DISTINCT(tr.object_id)
							FROM DB-PREFIX_term_relationships AS tr
							WHERE tr.term_taxonomy_id IN (1)) AND relevanssi.doc NOT IN (5,6007,2333,2488,6,4,10445) AND ((relevanssi.doc IN (SELECT DISTINCT(posts.ID) FROM DB-PREFIX_posts AS posts
			WHERE posts.post_type NOT IN ('revision', 'nav_menu_item', 'custom_css', 'customize_changeset', 'oembed_cache', 'user_request', 'wp_block', 'et_pb_layout', 'et_theme_builder', 'et_template', 'et_header_layout', 'et_body_layout', 'et_footer_layout'))) OR (doc = -1)) made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, apply_filters_ref_array('posts_pre_query'), WP_Hook->apply_filters, relevanssi_query, relevanssi_do_query, relevanssi_search

So we wanted to ask you if there is any cause for concern and, if so, if you could suggest adjustments we should make in our BPS Pro settings or our security settings in general on these sites due to this? Or is there nothing to do and we can just ignore this moving forward (although it is a bit of nuisance and not ideal to us to have our PHP Error logs filled up like this)?

Any information or advice you can offer is greatly appreciated. Thank you.

[AITpro Admin]

Yep, you are correct.  This is a nuisance spam thing.  The Query is not dangerous or even an attack.  I get these from time to time on my sites.  At some point I’ll look into trying to filter these spam strings from being logged in the php error log or figure out how the spammer is doing the spam Query and block it.  You can safely ignore these spam log entries.

[Living Miracles]

Okay, sounds good. Thank you very much for confirming that for us!

[AITpro Admin]

As it turns out this is not some sort of external spam attack thing.  A spammer posted or attempted to post spam on your site and the php error occurred because you have 2 different types of database table collations (utf8_general_ci and utf8mb4_unicode_520_ci), which is very common with WordPress.  For WordPress sites, the recommended charset is utf8mb4 and the recommended collation is utf8mb4_unicode_520_ci as of 5 years ago > https://core.trac.wordpress.org/ticket/32105. How to change database collation using phpMyAdmin > https://support.cpanel.net/hc/en-us/articles/360056173654-How-to-change-the-collation-or-character-set-for-your-database-in-cPanel-. Important Note: Make a backup of your database before you change your database collation.

[AITpro Admin]

These steps are better/faster > https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation. I just tested this on my Blog site and no problems. Took only seconds to change all DB tables using the steps below.

If you’re using PhpMyAdmin, you can now:

1. Select the database.
2. Click the “Operations” tab.
3. Under “Collation” section, select the desired collation.
4. Click the “Change all tables collations” checkbox.
5. A new “Change all tables columns collations” checkbox will appear.
6. Click the “Change all tables columns collations” checkbox.
7. Click the “Go” button.

[Living Miracles]

Great, thank you for replying to us about this and letting us know! We’ll probably try those instructions out in the coming days on the sites we’ve been noticing these types of PHP error log entries occurring on.

[Author]

Posts