Artem
08/04/2022, 10:01 PM{"component":"http","err":"select host software: Error 1267: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='","level":"error","method":"GET","took":"935.677µs","ts":"2022-08-04T21:52:52.216165861Z","uri":"/api/latest/fleet/software?page=0\u0026per_page=20\u0026order_key=hosts_count\u0026order_direction=desc","user":"artem"}
{"component":"http","err":"count host software: Error 1267: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='","level":"error","method":"GET","took":"814.587µs","ts":"2022-08-04T21:52:52.216283256Z","uri":"/api/latest/fleet/software/count?scope=softwareCount","user":"artem}
Could you please help me to find the way to solve it?
It’s possible to recreate a several tables, but I’m not sure about the results.Benjamin Edwards
08/04/2022, 10:06 PMArtem
08/04/2022, 10:07 PMMichal Nicpon
08/04/2022, 10:27 PMSELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fleet';
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fleet';
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'fleet';
Artem
08/04/2022, 10:48 PMMichal Nicpon
08/04/2022, 10:49 PMutf8mb4_0900_ai_ci
or utf8_general_ci
.Artem
08/04/2022, 10:53 PMutf8mb4_0900_ai_ci
, and everything works fine now.
Now I understand that default collation for Fleet tables is utf8mb4_general_ci
but my MySQL has different one almost everywhere.
Thank you so much for your help!Michal Nicpon
08/04/2022, 10:57 PMutf8mb4_0900_ai_ci
, but you would have to ask your DBA. We assume the collation is utf8mb4_general_ci
everywhereArtem
08/05/2022, 6:23 AMutf8mb4_general_ci
everywhere in this DB.