Hi again I see such errors when try to open software page in

Question

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 · Answer

What version of MySQL are you running

Artem · Answer

< Benjamin Edwards> hello 8 0 26 17

Artem · Answer

Same problem for host details page by id

Michal Nicpon · Answer

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 ```

Michal Nicpon · Answer

and ```SELECT TABLE CATALOG TABLE SCHEMA TABLE NAME TABLE COLLATION FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA = fleet ```

Michal Nicpon · Answer

Can you run one more query ```SELECT DEFAULT CHARACTER SET NAME DEFAULT COLLATION NAME FROM INFORMATION SCHEMA SCHEMATA WHERE SCHEMA NAME = fleet ```

Artem · Answer

< Michal Nicpon> sure done

Michal Nicpon · Answer

Do you know if the default database collation was changed at any point perhaps to `utf8mb4 0900 ai ci` or `utf8 general ci`

Artem · Answer

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 · Answer

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

Artem · Answer

< Michal Nicpon> got it I ll try to talk with them and change collation to `utf8mb4 general ci` everywhere in this DB