Error “Illegal mix of collations” after update to 2.4.3

  • Resolved Jan

    (@jax355)


    3 years, 10 months ago

    Hi there,

    after update to 2.4.3 we get a lot of errors like this:

    2021/01/28 13:23:35 [error] 5221#5221: *357701 FastCGI sent in stderr: "PHP message: WordPress-Datenbank-Fehler Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' für Abfrage SELECT c.*, h.hash FROM 'wp_xxxxxxxxxxxx_avatar_privacy' c LEFT OUTER JOIN 'wp_xxxxxxxxxxxx_avatar_privacy_hashes' h ON c.email = h.identifier AND h.type = "comment" WHERE 'email' = "[email protected]" von require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/cheerup/woocommerce.php'), woocommerce_content, wc_get_template_part, load_template, require('/plugins/woocommerce/templates/content-single-product.php'), do_action('woocommerce_after_single_product_summary'), WP_Hook->do_action, WP_Hook->apply_filters, woocommerce_output_product_data_tabs, wc_get_template, include('/plugins/woocommerce/templates/single-product/tabs/tabs.php'), comments_template, require('/plugins/woocommerce/templates/single-product-reviews.php'), wp_list_comments, Walker->paged_walk, Walker_Comment->display_element, Walk...PHP message: WordPress-Datenbank-Fehler Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' für Abfrage SELECT c.*, h.hash FROM 'wp_xxxxxxxxxxxx_avatar_privacy' c LEFT OUTER JOIN 'wp_xxxxxxxxxxxx_avatar_privacy_hashes' h ON c.email = h.identifier AND h.type = "comment" WHERE 'email' = "[email protected]" von require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/cheerup/woocommerce.php'), woocommerce_content, wc_get_template_part, load_template, require('/plugins/woocommerce/templates/content-single-product.php'), do_action('woocommerce_after_single_product_summary'), WP_Hook->do_action, WP_Hook->apply_filters, woocommerce_output_product_data_tabs, wc_get_template, include('/plugins/woocommerce/templates/single-product/tabs/tabs.php'), comments_template, require('/plugins/woocommerce/templates/single-product-reviews.php'), wp_list_comments, Walke

    What happend here and what can we do?

    Cheers, Jax

    • This topic was modified 3 years, 10 months ago by Jan.
Viewing 10 replies - 1 through 10 (of 10 total)
  • Plugin Author pepe

    (@pputzer)

    Hi @jax355, that’s an interesting one. Have you recently upgraded your MySQL/MariaDB server? What version of Avatar Privacy was installed before upgrading to 2.4.3?

    It looks like the two tables used by Avatar Privacy have mismatching collations set. Since we use the default collations suggested by WordPress, those appear to have changed between the first installation of Avatar Privacy and the creation of the second table (with the upgrade to 2.4.x). There is currently a on open ticket for this in Core.

    Is it possible for you to check the database table definitions for <prefix>_avatar_privacy and <prefix>_avatar_privacy_hashes? I’d like to verify which table got which collation.

    Thread Starter Jan

    (@jax355)

    Hi Pepe,

    thank you for your message.

    We didn’t any recent upgrades to the DB server and the previous version of the plugin installed was 2.3.4

    Plugin Author pepe

    (@pputzer)

    Can you please check which DB version you are using and which table has which collation set?

    Thread Starter Jan

    (@jax355)

    The DB version is 5.5.5-10.2.33-MariaDB-10.2.33

    Where can I find the collation set used?

    • This reply was modified 3 years, 10 months ago by Jan.
    Plugin Author pepe

    (@pputzer)

    Thank you.

    SHOW TABLE STATUS LIKE '%avatar_privacy%'; should give the status information for all Avatar Privacy tables, including the collation.

    Plugin Author pepe

    (@pputzer)

    @jax355 I’ve created a beta version of 2.4.4 that should fix things. I’ll test that on my own site to make sure it does not accidentally break anything (database changes are notoriously easy to mess up). If all goes well in this limited test, I can release it tomorrow.

    Thread Starter Jan

    (@jax355)

    Hi Pepe,

    sounds good.

    The tables have the following collations

    _avatar_privacy: utf8mb4_unicode_520_ci
    _avatar_privacy_hashes: utf8mb4_unicode_ci

    Plugin Author pepe

    (@pputzer)

    Interesting. This is the opposite of what I would have expected. (avatar_privacy_hashes was introduced with Avatar Privacy 2.4.0 and utf8mb4_unicode_520_ci is the more recent collatoin algorithm.

    Plugin Author pepe

    (@pputzer)

    @jax355, I’ve released 2.4.4.

    Thread Starter Jan

    (@jax355)

    Perfect, thanks a ton!

    Now both are utf8mb4_unicode_ci

    • This reply was modified 3 years, 9 months ago by Jan.
Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘Error “Illegal mix of collations” after update to 2.4.3’ is closed to new replies.