title: Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
body: <p>Alright, I give up. I'm 2 days into this error and I need help.</p>
  
  <p>Disclaimer: I will need help improving this question and will try to do a good job of describing the issue at hand, what I have done so far to address the issue, and share blog posts and the documentation that I have read in search of a solution.</p>
  
  <p>The Question (also, asked below in context): </p>
  
  <blockquote>
    <p>So the question is, why does the same query behave differently when
    run from Rails instead of from the mysql command line? Specifically,
    where is "(utf8_general_ci,COERCIBLE)" coming from?</p>
  </blockquote>
  
  <p>The Problem: <code>Autoresponder.find_by(keyword: '')</code> fails with the following error:</p>
  
  <pre><code>ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations 
  (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) 
  for operation '=': 
  SELECT  `autoresponders`.* 
  FROM `autoresponders`  
  WHERE `autoresponders`.`keyword` = '' 
  LIMIT 1
  </code></pre>
  
  <p><code>Autoresponder</code> is a model that has the attribute <code>keyword</code></p>
  
  <p>I read that I needed to specify my collation. So I tested out the following code:</p>
  
  <pre><code>Autoresponder.where('keyword collate utf8mb4_unicode_ci = ?', '')
  </code></pre>
  
  <p>and got the following error:</p>
  
  <pre><code>Illegal mix of collations 
  (utf8mb4_unicode_ci,EXPLICIT) and (utf8_general_ci,COERCIBLE) 
  for operation '=': 
  SELECT `autoresponders`.* 
  FROM `autoresponders`  
  WHERE (keyword collate utf8mb4_unicode_ci = '')
  </code></pre>
  
  <p>All that did was change the collation from IMPLICIT to EXPLICIT.</p>
  
  <p>I tried running the query in Sequel Pro and it worked (with and without the collate keyword). For clarity's sake here's the query:</p>
  
  <pre><code>SELECT `autoresponders`.* 
  FROM `autoresponders`  
  WHERE (keyword collate utf8mb4_unicode_ci = '');
  
  SELECT `autoresponders`.* 
  FROM `autoresponders`  
  WHERE (keyword = ' ');
  </code></pre>
  
  <p>AND it works! The queries ran without errors. I also ran <code>mysql</code> and was able to run the query there too. But I noticed something when I pasted the query into the <code>mysql</code> command line. It automatically used the Unicode name for the character instead of the actual character. Here is the query as observed in the mysql command line:</p>
  
  <pre><code>SELECT `autoresponders`.* 
  FROM `autoresponders`  
  WHERE (keyword collate utf8mb4_unicode_ci ='\U+1F615');
  </code></pre>
  
  <p>This query works.</p>
  
  <p>So the question is, why does the same query fail in Rails but work in Sequel Pro? Specifically, where is "(utf8_general_ci,COERCIBLE)" coming from, and how do I fix this mess? </p>
  
  <p>I thought that it might be coming from ActiveRecord, but running <code>ActiveRecord::Base.connection.collation</code> in rails console returns <code>utf8mb4_unicode_ci</code></p>
  
  <p>Here are my db character encoding and collation variables (and the query that retrieved them).</p>
  
  <pre><code>SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
  character_set_client        utf8mb4
  character_set_connection    utf8mb4
  character_set_database      utf8mb4
  character_set_filesystem    binary
  character_set_results       utf8mb4
  character_set_server        latin1
  character_set_system        utf8
  collation_connection        utf8mb4_unicode_ci
  collation_database          utf8mb4_unicode_ci
  collation_server            latin1_swedish_ci
  </code></pre>
  
  <p>Here is the create syntax for the Autorsponders table:</p>
  
  <pre><code>CREATE TABLE `autoresponders` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `keyword` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '',
    `body` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `provisioned_number_id` int(11) DEFAULT NULL,
    `outgoing_provisioned_number_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
  </code></pre>
  
  <p>Context: Rails 4.0.13, Mysql version 5.6.22-1+deb.sury.org~precise+1-log</p>
  
  <p>Here are some blog posts and SO articles that I have read so far:
  <a href="https://mathiasbynens.be/notes/mysql-utf8mb4" rel="noreferrer">https://mathiasbynens.be/notes/mysql-utf8mb4</a></p>
  
  <p><a href="http://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/" rel="noreferrer">http://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1566602/is-set-character-set-utf8-necessary">Is &quot;SET CHARACTER SET utf8&quot; necessary?</a></p>
  
  <p><a href="https://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic">Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation &#39;=&#39;</a></p>
  
  <p><a href="https://stackoverflow.com/questions/5052051/not-case-sensitive-search-with-active-record">Not case sensitive search with active record</a></p>
  
  <p><a href="https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_collation_server" rel="noreferrer">https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_collation_server</a></p>
  
  <p>All of this led me to create this meme:</p>
  
  <p><a href="https://i.stack.imgur.com/oOHqh.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/oOHqh.jpg" alt="enter image description here"></a></p>
  
  <p>Sincerely, </p>
  
  <p>An exhausted fellow developer.</p>
  
  <p>Thanks.</p>