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:

• WordPress database error COLLATION utf8_general_ci is not valid for CHARACTER SET utf8mb4 for query pronamic/wp-pronamic-pay-mollie#32 (comment)

To make this work in SQLite, I think we can omit the BINARY part. But as I already mentioned in #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

[remcotolsma]

I just read that BINARY notation as we use it is deprecated in MySQL:

The BINARY operator is deprecated; you should expect its removal in a future version of MySQL. Use CAST(... AS BINARY) instead.

https://dev.mysql.com/doc/refman/8.3/en/cast-functions.html#operator_binary

So this is probably not something that necessarily needs to be captured within this plugin.