connectionCollation being igored

[Stefan von Chossy]

Hi there,

we do have a mySql (5.6.37) db with the following server settings:

character-set-server = 'utf8mb4'
collation-server = 'utf8mb4_bin'

table(s) collation is also set to utf8mb4_bin.


Now for the JDBC (mysql-connector-java-5.1.39-bin.jar) client connection we use these settings:

<connection-property name="useUnicode">true</connection-property>
<connection-propertyname="connectionCollation">UTF8MB4_GENERAL_CI</connection-property>
<connection-property name="characterSetResults">utf8</connection-property>
<driver>MySQL</driver>

(Alternatively I've tried this as well: <connection-url>jdbc:mysql://x.x.x.x:3306/test?useUnicode=true&amp;connectionCollation=UTF8MB4_GENERAL_CI&amp;characterSetResults=utf8&amp;characterEncoding=utf8</connection-url>
)

The client connection properties seem to be ignored since any query initiated through this connection is still case sensitive (due to the utf8mb4_bin).

If I use the COLLATE directly at a query (e.g. "select * from table where value = 'aa' COLLATE utf8mb4_general_ci") everything works as expected and results with 'AA', 'Aa', 'aA' and 'aa' are returned.

Now I wonder why the JDBC client connectionCollation setting is being ignored. With this setting in place, I'd expect that I don't have to use the "select ... COLLATE utf8mb4_general_ci" clause.

Any thoughts on that?

Regards,

Stefan