|
calthoracic
|
November 16, 2023 at 1:43 pm
Hello,
I’ve migrated calthoracic.org to Kinsta (w previously working setup EE and Authorize.net) and I’m testing away while it’s not published (https://calthoracic2.kinsta.cloud/). I’m getting the following error while navigating to the Payments Method page. all plugins are updated. [PHP 8.0.30 WordPress 6.4.1 Betheme 27.2.9.4 Child theme DB Server 10.11.5-MariaDB]. My intention is to set up the Authorize.net sandbox acct and test. We migrated bc the old site was PHP 7.2.24 and EE version 5.0.3 could not be update along with other plugins. original db 5.5.68-MariaDB
Is this a DB configuration issue or other, please advise. thanks, Walt
A RuntimeException was thrown! code: EE_Admin_Page – _route_admin_request – 1156
Page route “_payment_methods_list” with the supplied arguments () threw the following exception: WPDB Error: “Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘='”
Notice: WPDB Error “Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘='” while running wpdb method “get_results” with arguments [“SELECT Currency.CUR_code AS ‘Currency.CUR_code’, Currency.CUR_single AS ‘Currency.CUR_single’, Currency.CUR_plural AS ‘Currency.CUR_plural’, Currency.CUR_sign AS ‘Currency.CUR_sign’, Currency.CUR_dec_plc AS ‘Currency.CUR_dec_plc’, Currency.CUR_active AS ‘Currency.CUR_active’, Currency_Payment_Method.CPM_ID AS ‘Currency_Payment_Method.CPM_ID’, Currency_Payment_Method.CUR_code AS ‘Currency_Payment_Method.CUR_code’, Currency_Payment_Method.PMD_ID AS ‘Currency_Payment_Method.PMD_ID’, Payment_Method.PMD_ID AS ‘Payment_Method.PMD_ID’, Payment_Method.PMD_type AS ‘Payment_Method.PMD_type’, Payment_Method.PMD_name AS ‘Payment_Method.PMD_name’, Payment_Method.PMD_desc AS ‘Payment_Method.PMD_desc’, Payment_Method.PMD_admin_name AS ‘Payment_Method.PMD_admin_name’, Payment_Method.PMD_admin_desc AS ‘Payment_Method.PMD_admin_desc’, Payment_Method.PMD_slug AS ‘Payment_Method.PMD_slug’, Payment_Method.PMD_order AS ‘Payment_Method.PMD_order’, Payment_Method.PMD_debug_mode AS ‘Payment_Method.PMD_debug_mode’, Payment_Method.PMD_wp_user AS ‘Payment_Method.PMD_wp_user’, Payment_Method.PMD_open_by_default AS ‘Payment_Method.PMD_open_by_default’, Payment_Method.PMD_button_url AS ‘Payment_Method.PMD_button_url’, Payment_Method.PMD_scope AS ‘Payment_Method.PMD_scope’ FROM wp_esp_currency AS Currency \n LEFT JOIN wp_esp_currency_payment_method AS Currency_Payment_Method \n ON Currency_Payment_Method.CUR_code=Currency.CUR_code \n LEFT JOIN wp_esp_payment_method AS Payment_Method \n ON Payment_Method.PMD_ID=Currency_Payment_Method.PMD_ID WHERE Payment_Method.PMD_ID = 10″,”ARRAY_A”]. Automatically attempting to fix EE Core DB in /www/calthoracic2_926/public/wp-content/plugins/event-espresso-core-reg/core/db_models/EEM_Base.model.php on line 2521
Notice: WPDB AGAIN: Error “Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘='” while running the same method and arguments as before. Automatically attempting to fix EE Addons DB in /www/calthoracic2_926/public/wp-content/plugins/event-espresso-core-reg/core/db_models/EEM_Base.model.php on line 2521
|
|
calthoracic
|
November 16, 2023 at 2:21 pm
fyi – running the query results in the following:
Normally I would try and fix/update the query w COLLATE clause but the query is coming from EE code. Do I have to alter the tables?
Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’
SELECT
Currency.CUR_code AS ‘Currency.CUR_code’,
Currency.CUR_single AS ‘Currency.CUR_single’,
Currency.CUR_plural AS ‘Currency.CUR_plural’,
Currency.CUR_sign AS ‘Currency.CUR_sign’,
Currency.CUR_dec_plc AS ‘Currency.CUR_dec_plc’,
Currency.CUR_active AS ‘Currency.CUR_active’,
Currency_Payment_Method.CPM_ID AS ‘Currency_Payment_Method.CPM_ID’,
Currency_Payment_Method.CUR_code AS ‘Currency_Payment_Method.CUR_code’,
Currency_Payment_Method.PMD_ID AS ‘Currency_Payment_Method.PMD_ID’,
Payment_Method.PMD_ID AS ‘Payment_Method.PMD_ID’,
Payment_Method.PMD_type AS ‘Payment_Method.PMD_type’,
Payment_Method.PMD_name AS ‘Payment_Method.PMD_name’,
Payment_Method.PMD_desc AS ‘Payment_Method.PMD_desc’,
Payment_Method.PMD_admin_name AS ‘Payment_Method.PMD_admin_name’,
Payment_Method.PMD_admin_desc AS ‘Payment_Method.PMD_admin_desc’,
Payment_Method.PMD_slug AS ‘Payment_Method.PMD_slug’,
Payment_Method.PMD_order AS ‘Payment_Method.PMD_order’,
Payment_Method.PMD_debug_mode AS ‘Payment_Method.PMD_debug_mode’,
Payment_Method.PMD_wp_user AS ‘Payment_Method.PMD_wp_user’,
Payment_Method.PMD_open_by_default AS ‘Payment_Method.PMD_open_by_default’,
Payment_Method.PMD_button_url AS ‘Payment_Method.PMD_button_url’,
Payment_Method.PMD_scope AS ‘Payment_Method.PMD_scope’
FROM
wp_esp_currency AS Currency
LEFT JOIN
wp_esp_currency_payment_method AS Currency_Payment_Method ON Currency_Payment_Method.CUR_code = Currency.CUR_code
LEFT JOIN
wp_esp_payment_method AS Payment_Method ON Payment_Method.PMD_ID = Currency_Payment_Method.PMD_ID
WHERE
Payment_Method.PMD_ID = 10;
|
|
calthoracic
|
November 16, 2023 at 2:23 pm
fyi – running the query against the backend results in the following:
Error Code: 1267. Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’
Normally I would try and fix/update the query w a COLLATE clause but the query is coming from EE code. Do I have to alter the tables?
please advise, Walt
|
|
calthoracic
|
November 16, 2023 at 2:45 pm
fyi – adding COLLATE resolves the error
FROM
wp_esp_currency AS Currency — utf8mb4_unicode_ci
LEFT JOIN wp_esp_currency_payment_method AS Currency_Payment_Method — utf8mb4_unicode_520_ci
ON Currency_Payment_Method.CUR_code COLLATE utf8mb4_unicode_520_ci = Currency.CUR_code — utf8mb4_unicode_ci
LEFT JOIN wp_esp_payment_method AS Payment_Method — utf8mb4_unicode_ci
ON Payment_Method.PMD_ID COLLATE utf8mb4_unicode_520_ci = Currency_Payment_Method.PMD_ID; — utf8mb4_unicode_520_ci
|
|
Tony
|
November 16, 2023 at 3:42 pm
Hi there,
Whilst EE’s query is showing the symptom the problem is you have mixed collations within the DB.
The tale needs to be updated to use the ‘correct’ colation and the easiest way to do that is using a plugin like this:
https://wordpress.org/plugins/database-collation-fix/
Or, (based on your replies) it sounds like you are comfortable enough working within the DB so you could alter the table manually.
|
|
calthoracic
|
November 16, 2023 at 4:33 pm
Tony,
Thanks, so altering the table is the route to go. Can I assume if we were doing a fresh EE install (not migrating from old table structures) the tables would be in synch (i.e. correct/equal collation types).
That said, make the all relevant tables collation to utf8mb4_unicode_ci?
current table collation:
wp_esp_currency — utf8mb4_unicode_ci
wp_esp_currency_payment_method — utf8mb4_unicode_520_c
wp_esp_payment_method — utf8mb4_unicode_ci
meaning alter wp_esp_currency_payment_method to utf8mb4_unicode_ci?
thanks, Walt
|
|
Tony
|
November 17, 2023 at 3:14 am
Can I assume if we were doing a fresh EE install (not migrating from old table structures) the tables would be in synch (i.e. correct/equal collation types).
Correct, EE creates the table on the fly but we use:
$SQL = "CREATE TABLE $table_name ( $create_sql ) ENGINE=$engine " . $wpdb->get_charset_collate();
So the table will use whatever collection is set to be used at that time.
meaning alter wp_esp_currency_payment_method to utf8mb4_unicode_ci?
Correct.
|
|
calthoracic
|
November 17, 2023 at 11:30 am
Tony, thanks for your reply and confirmation!!
|
|
Tony
|
November 17, 2023 at 2:24 pm
You’re most welcome.
Did the above work as expected?
|
