(SOLVED) Re: SQLgrey encountered an SQL error ...
Posted: 09 Apr 2019 08:05
USE sqlgrey;
SHOW VARIABLES LIKE '%collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+
As you can see sqlgrey create its own database in latin1 but now with mariadb EFA uses utf8 connection, this give the error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE)
Other DB in EFA are altrady utf8
We need to change database, but also tables and columns to be compatible with connection
Here sequence , worked for me
You can do it without problem
mysql -u root -pXXXXXXXXXXXXXX
USE sqlgrey;
ALTER DATABASE sqlgrey CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE config CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE connect CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE domain_awl CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE from_awl CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optin_domain CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optin_email CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optout_domain CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optout_email CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE config MODIFY parameter varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE config MODIFY value varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY sender_name varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY sender_domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY src varchar(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY rcpt varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE domain_awl MODIFY sender_domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE domain_awl MODIFY src varchar(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE from_awl MODIFY sender_name varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE from_awl MODIFY sender_domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE from_awl MODIFY src varchar(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optin_domain MODIFY domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optin_email MODIFY email varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optout_domain MODIFY domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optout_email MODIFY email varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SHOW VARIABLES LIKE '%collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | utf8mb4_unicode_ci |
+----------------------+--------------------+
As you can see sqlgrey create its own database in latin1 but now with mariadb EFA uses utf8 connection, this give the error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE)
Other DB in EFA are altrady utf8
We need to change database, but also tables and columns to be compatible with connection
Here sequence , worked for me
You can do it without problem
mysql -u root -pXXXXXXXXXXXXXX
USE sqlgrey;
ALTER DATABASE sqlgrey CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE config CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE connect CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE domain_awl CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE from_awl CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optin_domain CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optin_email CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optout_domain CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE optout_email CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE config MODIFY parameter varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE config MODIFY value varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY sender_name varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY sender_domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY src varchar(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE connect MODIFY rcpt varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE domain_awl MODIFY sender_domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE domain_awl MODIFY src varchar(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE from_awl MODIFY sender_name varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE from_awl MODIFY sender_domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE from_awl MODIFY src varchar(39) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optin_domain MODIFY domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optin_email MODIFY email varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optout_domain MODIFY domain varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE optout_email MODIFY email varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;