WordPress database error Illegal mix of collations

  • angeliti

    (@angeliti)


    2 years ago

    Hello!

    I found out my error_log is full on database errors related to Relevanssi like this one:

    [06-Nov-2022 21:22:15 UTC] 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 * 1 + relevanssi.comment * 0.75 +
relevanssi.tag * 0.75 + relevanssi.link * 0 +
relevanssi.author + relevanssi.category * 0.75 + relevanssi.excerpt +
relevanssi.taxonomy + relevanssi.customfield + relevanssi.mysqlcolumn AS tf
FROM wp_relevanssi AS relevanssi WHERE relevanssi.term = '??ivermectin' ORDER BY tf DESC LIMIT 500 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

    Any ideas about why this is happening and how to fix it?

    Thanks,
    ángel.-

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Mikko Saari

    (@msaari)

    First of all, this problem only applies to searches with emojis, meaning it’s practically all spam.

    The problem is that your database collation for the wp_relevanssi table does not support emojis. You can fix this by changing the collation to something that supports emojis, like utf8mb4_unicode_ci.

    Relevanssi Premium has a spam blocking feature that can block all queries with emojis; that’s a good solution, too.

    Thread Starter angeliti

    (@angeliti)

    Thanks for your response, Mikko.

    When I try to change the table collation to utf8_unicode_ci I get the following error:

    #1062 – Duplicated entry ‘18949-estresado-0’ for key ‘PRIMARY’

    Any ideas of why and how to solve it?

    Thanks again,
    ángel.-

    Plugin Author Mikko Saari

    (@msaari)

    The easiest way to fix this is to truncate the whole table, change the collation and then rebuild the index.

    Thread Starter angeliti

    (@angeliti)

    Hmmmm… I know how to truncate the table and change the collation from my cpanel, but not how to rebuild the index.

    Will Relevanssi do this automatically after the truncation + change?

    Plugin Author Mikko Saari

    (@msaari)

    Just click the “Build the index” button in Settings > Relevanssi > Indexing.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘WordPress database error Illegal mix of collations’ is closed to new replies.