MySQL with JPA: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

Question

I need to be able to store characters like \xF0\x9F\x94\xA5 in my database, which, according to this post need UTF8mb4 encoding.

So I set up my database with

CREATE DATABASE `myDB` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

and verified in the MySQL shell if it was effective:

SHOW FULL COLUMNS FROM myTable;

+---------+------------------+--------------------+----
| Field   | Type             | Collation          | ...
+---------+------------------+--------------------+-----
| id      | int(10) unsigned | NULL               | ...   
| myColumn| text             | utf8mb4_general_ci | ...
+---------+------------------+--------------------+-----

So far so good.

After running my program, I got this Exception:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'
Error Code: 1267

For the record: I am using the Java Persistency API (JPA) in my webapplication with GlassFish 3.1. The Exception gets thrown when executing a named query:

@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c FROM myTable c WHERE c.myColumn LIKE :myColumn")

However, it appears that the error only occurs when the queried String actually contains those weired emoji characters (\xF0\x9F\x94\xA5)

Call: SELECT id, myColumn FROM myDB.myTable WHERE myColumn LIKE ?
bind => [Something something Lorem Ipsum 🇬🇧]  

So I thought, that somewhere might still be a utf8_general_ci set up and I tried putting the COLLATION directly into the query (as suggested in this post here)

@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c COLLATE utf8mb4_general_ci FROM myTable c WHERE c.myColumn LIKE :myColumn")

But still nothing.

Then I tried putting the collation directly in the connection (in GlassFish I am using a connection_pool) as I read here

characterEncoding, UTF8mb4

But GlassFish said only Connection could not be allocated because: Unsupported character encoding 'UTF8mb4'

The last thing I did was checking the Database System (I am using MariaDB)

show variables WHERE variable_name like "col%";
+----------------------+------------------+
| Variable_name        | Value            |
+----------------------+------------------+
| collation_connection | utf8_general_ci  |
| collation_database   | utf32_general_ci |
| collation_server     | utf8_general_ci  |
+----------------------+------------------+

And now I am completely lost...

What can I do to use utf8mb4 or utf-32 or anything else for that matter that is more advanced that simple UTF-8 ?

Answer 1

You do not need any change in the Java side, as utf8mb4 is just UTF-8 in Java.

Instead, as you can see here:

show variables WHERE variable_name like "col%";
+----------------------+------------------+
| Variable_name        | Value            |
+----------------------+------------------+
| collation_connection | utf8_general_ci  |
| collation_database   | utf32_general_ci |
| collation_server     | utf8_general_ci  |
+----------------------+------------------+

your connection setting is still utf8_general_ci; to set it at the connection level, one option is to execute the (mysql specific) query:

SET NAMES='utf8mb4'

before any attempt to use the utf8mb4 collation; or, generally for the mysql server, in /etc/my.cnf:

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Another option without changing the connection string, is using a jdbc driver version >= 5.1.13: http://www.opensubscriber.com/message/[email protected]/14151747.html