https://github.com/osquery/osquery logo
Join Slack
Powered by
Hi again! I see such errors when try to open softw...
# fleet
a
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.
b
What version of MySQL are you running?
a
@Benjamin Edwards hello! 8.0.26-17
Same problem for host details page by id
m
would you mind running the following query on the database and pasting the output?
Copy code
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'fleet';
and
Copy code
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fleet';
Can you run one more query
Copy code
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'fleet';
a
@Michal Nicpon sure, done!
m
Do you know if the default database collation was changed at any point? perhaps to 
utf8mb4_0900_ai_ci
or 
utf8_general_ci
.
a
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!
m
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
@Michal Nicpon got it, I’ll try to talk with them and change collation to 
utf8mb4_general_ci
everywhere in this DB.
6 Views
Open in Slack
PreviousNext
Powered by