Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE), (utf8mb4_0900_ai_ci,COERCIBLE)

[Fabrizio Gennari]

Hello,

I'm using MySQL.

A jOOQ query that used to work until recently suddenly started giving an error message

Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

The query is pretty trivial:

Mytable.MY_FIELD.eq("some string value")

Mytable has CHARSET=latin1. Column my_field is a varchar(2).

I already tried to force the collation on the field,

Mytable.MY_FIELD.collate("utf8mb4_0900_ai_ci").eq("some string value")

but it gives an error:

COLLATION 'utf8mb4_0900_ai_ci' is not valid for CHARACTER SET 'latin1'

To my knowledge the only change was an upgrade of the server to MySQL 8.0.

What is the way out? Is it possible, for example, to force the collation on the constant string itself? In SQL,

`column`="value" collate latin1_general_ci

is valid.

Regards,
Fabrizio

[Lukas Eder]

Hi Fabrizio,

Did you specify the correct character set and collation on your JDBC connection? The following properties seem relevant

- characterEncoding

- characterSetResults

- connectionCollation

See also:

- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-connection.html

- https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-session.html

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/ba974368-b98b-4251-8ea5-ddfb31caf2a1n%40googlegroups.com.

[Fabrizio Gennari]

Thanks for the answer. Eventually the problem has been solved by changing the CHARACTER SET of the column in the database to utf8mb4, and no code changes were necessary.

MySQL allows to set explicitly the character set and the collation of a string literal. Example (taken from MySQL docs)

SELECT _latin1'Müller' COLLATE latin1_german1_ci;

Is there a way to do something like that in jOOQ? Something like

COLUMN.eq(new StringWithCharset("value", DSL.characterSet("latin1"), DSL.collation("latin1_general_ci")))

[Lukas Eder]

Yes, the Field.collate() method that you've mentioned earlier is the way to go.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9b6b1d46-a9ad-4dc6-913e-46ea42fd6f79n%40googlegroups.com.

[Fabrizio Gennari]

If I understood correctly,

Mytable.MY_FIELD.collate("utf8mb4_0900_ai_ci").eq("some string value")

is the equivalent of the SQL

`my_field` COLLATE utf8mb4_0900_ai_ci = "some string value"

Is there a jOOQ equivalent of

`my_field` = "some string value" COLLATE utf8mb4_0900_ai_ci

that is, the collation is applied to the string literal, and not to the column value? And I have a similar question for character sets

[Lukas Eder]

Field.eq(String) is just syntax sugar for Field.eq(val(String)), so use DSL.val(String) directly to turn your String into an explicit bind variable of type Field<String>, see:

https://www.jooq.org/doc/latest/manual/sql-building/bind-values/

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d6b347bb-5880-4f61-b307-fe62124a0ce9n%40googlegroups.com.