• a

    Artem

    1 week ago
    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

    1 week ago
    What version of MySQL are you running?
  • a

    Artem

    1 week ago
    @Benjamin Edwards hello! 8.0.26-17
  • Same problem for host details page by id
  • Michal Nicpon

    Michal Nicpon

    1 week ago
    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';
  • and
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fleet';
  • Can you run one more query
    SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'fleet';
  • a

    Artem

    1 week ago
    @Michal Nicpon sure, done!
  • Michal Nicpon

    Michal Nicpon

    1 week ago
    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

    1 week ago
    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

    1 week ago
    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

    1 week ago
    @Michal Nicpon got it, I’ll try to talk with them and change collation to
    utf8mb4_general_ci
    everywhere in this DB.