Queries with INSERT INGORE are not working?

[remcotolsma]

We use the following query to convert WordPress user meta data to a custom table in a plugin:

INSERT IGNORE INTO wp_pronamic_pay_mollie_customers (
	mollie_id,
	test_mode
)
SELECT
	meta_value AS mollie_id,
	'_pronamic_pay_mollie_customer_id_test' = meta_key AS test_mode
FROM
	wp_usermeta
WHERE
	meta_key IN (
		'_pronamic_pay_mollie_customer_id',
		'_pronamic_pay_mollie_customer_id_test'
	)
		AND
	meta_value != ''
;

https://github.com/pronamic/wp-pronamic-pay-mollie/blob/82f0e72fedfdc11f706de64442a7f43c4ac96a62/src/Install.php#L313-L331

This results in the following error:

I think we can rewrite the query so the IGNORE is no longer needed.

Also INSERT OR IGNORE seems te work:

INSERT OR IGNORE INTO wp_pronamic_pay_mollie_customers (
	mollie_id,
	test_mode
)
SELECT
	meta_value AS mollie_id,
	'_pronamic_pay_mollie_customer_id_test' = meta_key AS test_mode
FROM
	wp_usermeta
WHERE
	meta_key IN (
		'_pronamic_pay_mollie_customer_id',
		'_pronamic_pay_mollie_customer_id_test'
	)
		AND
	meta_value != ''
;

https://stackoverflow.com/questions/12105198/sqlite-how-to-get-insert-or-ignore-to-work

This project is new to me, I have no idea if the SQL parser in this plugin should or could correct 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