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")))
On Monday, January 25, 2021 at 11:18:53 AM UTC+1 [email protected] wrote:
> 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
>
> On Fri, Jan 22, 2021 at 9:15 PM 'Fabrizio Gennari' via jOOQ User Group <
> [email protected]> wrote:
>
>>
>> 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
>>
>> --
>> 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 [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jooq-user/ba974368-b98b-4251-8ea5-ddfb31caf2a1n%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/jooq-user/ba974368-b98b-4251-8ea5-ddfb31caf2a1n%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/9b6b1d46-a9ad-4dc6-913e-46ea42fd6f79n%40googlegroups.com.
