1

I am connecting to a mysql database from node using mysql2 and have been experiencing intermittent illegal-mix-of-collations errors - see this question. The collation of the database is utf8mb4_0900_ai_ci.

I have tried adding charset: 'utf8mb4_0900_ai_ci' to my connection config:

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test',
    charset: 'utf8mb4_0900_ai_ci'
});

Based on this answer charset and collation are not interchangeable, which would suggest 'utf8mb4_0900_ai_ci' is an invalid charset value, but it seems to have fixed the problem and I'd prefer a solution that works at connection level, as it's cleaner that setting collation on individual columns in my queries (as advised here).

Is this a valid approach to resolving these issues? If not, is there a better solution?

The charset of the database is utf8mb4.

Matt Saunders
  • 3,538
  • 2
  • 22
  • 30
  • If the conflict is between two columns in your tables, no connection 'fix' exists. The connection property can only impact literal values in your queries. For example in `table.foo = 'bar'` the connection property explicitly states the collation to be used for `'bar'`, but has no impact on the tables and can not 'fix' conflicts such as `table1.foo = table2.bar` – MatBailie Apr 29 '22 at 10:03
  • Thanks for the advice @MatBailie - I've checked the tables and the collations are consistent, so I'd guess it is a literal value where the issue is arising? E.g. if I set the queried value as a variable (because it's used several times in the query) and then compare this with a column, could this throw an 'Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT)' error? – Matt Saunders Apr 29 '22 at 10:13

1 Answers1

0

charset: 'utf8mb4_0900_ai_ci'

That's a collation. The "charset" is utf8mb4.

The "connection" is stating what encoding (charset) is in use in the client. MySQL will convert between charsets if necessary, but it is based on the connection charset and the column charset.

Rick James
  • 135,179
  • 13
  • 127
  • 222