Title
#fleet
a

Artem

08/04/2022, 10:01 PM
Hi again! I see such errors when try to open software page in Fleet after update to 4.18.0
{"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

Benjamin Edwards

08/04/2022, 10:06 PM
What version of MySQL are you running?
a

Artem

08/04/2022, 10:07 PM
@Benjamin Edwards hello! 8.0.26-17
10:13 PM
Same problem for host details page by id
Michal Nicpon

Michal Nicpon

08/04/2022, 10:27 PM
would you mind running the following query on the database and pasting the output?
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fleet';
10:29 PM
and
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fleet';
10:43 PM
Can you run one more query
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'fleet';
a

Artem

08/04/2022, 10:48 PM
@Michal Nicpon sure, done!
Michal Nicpon

Michal Nicpon

08/04/2022, 10:49 PM
Do you know if the default database collation was changed at any point? perhaps to
utf8mb4_0900_ai_ci
or
utf8_general_ci
.
a

Artem

08/04/2022, 10:53 PM
Yea, I think it was done by our DBA team. I will ask them about it later. But thank you for your queries, they gave me better understanding about problem. Just for mitigation I changed several tables collation to
utf8mb4_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

Michal Nicpon

08/04/2022, 10:57 PM
There may have been a reason to change the collation to
utf8mb4_0900_ai_ci
, but you would have to ask your DBA. We assume the collation is
utf8mb4_general_ci
everywhere
a

Artem

08/05/2022, 6:23 AM
@Michal Nicpon got it, I’ll try to talk with them and change collation to
utf8mb4_general_ci
everywhere in this DB.