No support for `BINARY` compare e.g. `ON BINARY wp_user_meta.meta_value = mollie_customer.mollie_id`

[remcotolsma]

In a custom query we use the following notation:

ON BINARY wp_user_meta.meta_value = mollie_customer.mollie_id

Full query:

INSERT OR 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
		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 != ''
;

We use the BINARY compare to avoid issues like these:

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

• https://github.com/pronamic/wp-pronamic-pay-mollie/issues/32#issuecomment-1731595752

To make this work in SQLite, I think we can omit the BINARY part. But as I already mentioned in https://github.com/WordPress/sqlite-database-integration/issues/79, I have no idea whether the SQL parser in this plugin can or should solve this.

Table prefix	wp_
Database type	SQLite
SQLite version	3.40.1
Database file	/var/www/html/wp-content/database/.ht.sqlite
Database size	532 KB