SQL errors in logs

That’s not the full error message, and doesn’t actually have the error, so I can’t tell what is the problem here. What is the full error message you’re getting?

Anyway, based on those queries it looks like your SQL server does not like emoji. Relevanssi should handle emoji fine, all search queries are passed through wp_encode_emoji(), which should encode the emoji.

Did you upgrade from 4.12.4, or from an older version? If you’re seeing this error in 4.12.5, you should see it in 4.12.4 as well. A change related to this was made in 4.7.0, so if you upgraded from something older than that, that would explain.

As for a solution, one way might be to block all queries that include emoji. Turns out that might a bit tricky, but it’s something to look into.

Sorry, this is the error:

WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation ‘like’ for query

I do not know what version we upgraded from but we update pretty frequently so I doubt anywhere near that old.

• This reply was modified 3 years, 11 months ago by Matt.

I created the following to filter emoji’s from searches which seems to be working, maybe.

function asd_remove_emojis($string) {

    // Match Emoticons
    $regex_emoticons = '/[\x{1F600}-\x{1F64F}]/u';
    $clear_string = preg_replace($regex_emoticons, '', $string);

    // Match Miscellaneous Symbols and Pictographs
    $regex_symbols = '/[\x{1F300}-\x{1F5FF}]/u';
    $clear_string = preg_replace($regex_symbols, '', $clear_string);

    // Match Transport And Map Symbols
    $regex_transport = '/[\x{1F680}-\x{1F6FF}]/u';
    $clear_string = preg_replace($regex_transport, '', $clear_string);

    // Match Miscellaneous Symbols
    $regex_misc = '/[\x{2600}-\x{26FF}]/u';
    $clear_string = preg_replace($regex_misc, '', $clear_string);

    // Match Dingbats
    $regex_dingbats = '/[\x{2700}-\x{27BF}]/u';
    $clear_string = preg_replace($regex_dingbats, '', $clear_string);

    return $clear_string;
}

function asd_search_filter($query) {

	if ( !is_admin() && $query->is_main_query() ) {

		// Check if we are on a search page
		if ($query->is_search) {
		
			// Get the search query
			$search_term = get_search_query();
			
			$search_term = asd_remove_emojis($search_term);
			
			$query->set( 's', $search_term  );
		}
	}
}
add_action('pre_get_posts','asd_search_filter');

I wouldn’t actually remove the emoji, I’d just kill the process right there and then with exit() if there’s emoji in the search query – those aren’t legitimate searches in most cases anyway.

Based on the error, the actual solution for this problem is to fix the database collations. Your wp_relevanssi database table collation should be utf8mb4_unicode_520_ci, not utf8_general_ci.