title: MySQL with JPA: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
body: <p>I need to be able to store characters like <code>\xF0\x9F\x94\xA5</code> in my database, which, according to <a href="https://stackoverflow.com/a/13754976/896249">this post</a> need <code>UTF8mb4</code> encoding. </p>
  
  <p>So I set up my database with </p>
  
  <pre><code>CREATE DATABASE `myDB` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
  </code></pre>
  
  <p>and verified in the MySQL shell if it was effective:</p>
  
  <pre><code>SHOW FULL COLUMNS FROM myTable;
  
  +---------+------------------+--------------------+----
  | Field   | Type             | Collation          | ...
  +---------+------------------+--------------------+-----
  | id      | int(10) unsigned | NULL               | ...   
  | myColumn| text             | utf8mb4_general_ci | ...
  +---------+------------------+--------------------+-----
  </code></pre>
  
  <p>So far so good.</p>
  
  <p>After running my program, I got this <strong>Exception</strong>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>For the record</strong>: I am using the Java Persistency API (JPA) in my webapplication with GlassFish 3.1. The <code>Exception</code> gets thrown when executing a named query:</p>
  
  <pre><code>@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c FROM myTable c WHERE c.myColumn LIKE :myColumn")
  </code></pre>
  
  <p>However, it appears that the error only occurs when the queried String actually contains those weired emoji characters (<code>\xF0\x9F\x94\xA5</code>)</p>
  
  <pre><code>Call: SELECT id, myColumn FROM myDB.myTable WHERE myColumn LIKE ?
  bind =&gt; [Something something Lorem Ipsum ]  
  </code></pre>
  
  <p>So I thought, that somewhere might still be a <code>utf8_general_ci</code> set up and I tried putting the <code>COLLATION</code> directly into the query (as suggested <a href="https://stackoverflow.com/a/183624/896249">in this post here</a>)</p>
  
  <pre><code>@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c COLLATE utf8mb4_general_ci FROM myTable c WHERE c.myColumn LIKE :myColumn")
  </code></pre>
  
  <p>But still nothing.</p>
  
  <p>Then I tried putting the collation directly in the connection (in GlassFish I am using a <em>connection_pool</em>) as I <a href="https://stackoverflow.com/a/20546571/896249">read here</a></p>
  
  <pre><code>characterEncoding, UTF8mb4
  </code></pre>
  
  <p>But GlassFish said only <code>Connection could not be allocated because: Unsupported character encoding 'UTF8mb4'</code></p>
  
  <p>The last thing I did was checking the Database System (I am using MariaDB)</p>
  
  <pre><code>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  |
  +----------------------+------------------+
  </code></pre>
  
  <p>And now I am completely lost... </p>
  
  <p>What can I do to use <code>utf8mb4</code> or <code>utf-32</code> or anything else for that matter that is more advanced that simple UTF-8 ?  </p>