WordPress database error COLLATION `utf8_general_ci` is not valid for CHARACTER SET `utf8mb4` for query

[rvdsteege]

An user encountered the following error on plugin activation in a WordPress multisite:

WordPress database error COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' for query:

INSERT IGNORE INTO wp_pronamic_pay_mollie_customer_users (
    customer_id,
    user_id
)
            SELECT
                mollie_customer.id AS mollie_customer_id,
                wp_user.ID AS wp_user_id
            FROM
                wp_pronamic_pay_mollie_customers AS mollie_customer
                    INNER JOIN
                wp_usermeta AS wp_user_meta
                        ON wp_user_meta.meta_value = mollie_customer.mollie_id COLLATE utf8_general_ci
                    INNER JOIN
                wp_users AS wp_user
                        ON wp_user_meta.user_id = wp_user.ID
            WHERE
                wp_user_meta.meta_key IN (
    '_pronamic_pay_mollie_customer_id',
    '_pronamic_pay_mollie_customer_id_test'
)
AND
wp_user_meta.meta_value != ''
;

This query is from the gateway install:

https://github.com/pronamic/wp-pronamic-pay-mollie/blob/6653500101afc5b96de8f77fb2ded9e662d27730/src/Install.php#L345-L390

The collation in $wpdb->collate is determined in:

• https://github.com/WordPress/WordPress/blob/9e0f2faa28f1aa04a69e0b4eaa410a38adcd2e1e/wp-includes/class-wpdb.php#L836-L911

However, I would expect it to result in something utf8mb4-ish there (utf8mb4_unicode_520_ci in my non-multisite development environment)?

According to site health, constants are set as follows:

• DB_CHARSET: utf8
• DB_COLLATE: undefined

In this case, explicitly setting the DB_COLLATE constant resolved the issue:

define( 'DB_COLLATE', 'utf8mb4_unicode_520_ci' );

Internal Help Scout ticket: https://secure.helpscout.net/conversation/2258876225/25718

[remcotolsma]

I couldn't reproduce this on a clean install. Adding the collate was once a fix for a problem: https://github.com/pronamic/wp-pronamic-pay-mollie/commit/593e4ff9f38405cd2d5c64c73c1dad6a5eb36a24. Perhaps the collate differed per table and that caused problems? That may also be difficult to catch, but should we throw an exception for errors with this query?

https://github.com/pronamic/wp-pronamic-pay-mollie/blob/6653500101afc5b96de8f77fb2ded9e662d27730/src/Install.php#L392-L399

[rvdsteege]

I'm also unable to reproduce it. Probably better to not throw an exception then?

[remcotolsma]

I removed the collate clause, maybe the issue back in Mar 27, 2020 was related to: https://bugs.mysql.com/bug.php?id=101891

Currently i can't reproduce the following error:

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='.

By removing the COLLATE utf8_general_ci clause i think we solved this issue.

Removing the exception might not be a good idea, the meta convert routine might be crucial for subscription websites.

[rvdsteege]

Just received a message about the same error occurring again:

Uncaught Exception: Could not convert user meta, database error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='. in /public_html/wp-content/plugins/pronamic-ideal/packages/wp-pay-gateways/mollie/src/Install.php:375

Internal HelpScout ticket: https://secure.helpscout.net/conversation/2367890922/26189/

[rvdsteege]

From what I understand, the issue is caused by the fact that the wp_usermeta was originally created with a version of MySQL older than MySQL version 5.6.0 and that the current MySQL version is >= 5.6.0. This causes the collation determined by WordPress being changed from utf8mb4_unicode_ci to utf8mb4_unicode_520_ci:

• https://github.com/WordPress/WordPress/blob/e3954daefa4bad4bc07e3619080bf7ba3ced3465/wp-includes/class-wpdb.php#L884-L887
• https://github.com/WordPress/WordPress/blob/e3954daefa4bad4bc07e3619080bf7ba3ced3465/wp-includes/class-wpdb.php#L4100-L4101

Because the wp_usermeta.meta_value column then has the utf8mb4_unicode_ci collation and wp_pronamic_pay_mollie_customers.mollie_id the utf8mb4_unicode_520_ci, the "Illegal mix of collations" error occurs.

I found that if we want to ignore collations in this query, we can also use BINARY like so:

    INNER JOIN
        $wpdb->usermeta AS wp_user_meta
            ON BINARY wp_user_meta.meta_value = mollie_customer.mollie_id

I think that would be beneficial for this query, to prevent future collation issues.

To modify the collation of the wp_pronamic_pay_mollie_customers.mollie_id column, I used the following query:

ALTER TABLE `wp_pronamic_pay_mollie_customers` MODIFY `mollie_id` VARCHAR(16) COLLATE 'utf8mb4_unicode_ci';

[remcotolsma]

Added in https://github.com/pronamic/wp-pronamic-pay-mollie/commit/fd373d18ab2ee08567b75042d2c72da4121d0ed6.