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:

wp-pronamic-pay-mollie/src/Install.php

Lines 345 to 390 in 6653500

	/**
	* Collate caluse.
	*
	* Force a specific collate to fix:
	* "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and
	* (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='. ".
	*
	* @link https://dev.mysql.com/doc/refman/8.0/en/charset-collate.html
	*/
	$collate_clause = '';
	if ( ! empty( $wpdb->collate ) ) {
	$collate_clause = \sprintf(
	'COLLATE %s',
	$wpdb->collate
	);
	}
	$query = "
	INSERT IGNORE INTO $wpdb->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
	$wpdb->pronamic_pay_mollie_customers AS mollie_customer
	INNER JOIN
	$wpdb->usermeta AS wp_user_meta
	ON wp_user_meta.meta_value = mollie_customer.mollie_id $collate_clause
	INNER JOIN
	$wpdb->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 != ''
	;
	";
	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared.
	$result = $wpdb->query( $query );

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: 593e4ff. 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?

wp-pronamic-pay-mollie/src/Install.php

Lines 392 to 399 in 6653500

	if ( false === $result ) {
	throw new \Exception(
	sprintf(
	'Could not convert user meta, database error: %s.',
	$wpdb->last_error
	)
	);
	}

[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 fd373d1.