title: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) within stored procedure
body: <ol>
  <li><p>All the tables are in <code>utf_unicode_ci</code>.</p>
  
  <p>I've done this to check</p>
  
  <pre><code>SELECT table_schema, table_name, column_name, character_set_name, collation_name
      FROM information_schema.columns
  WHERE collation_name &lt;&gt; 'utf8_unicode_ci' AND table_schema LIKE 'my_database'
      ORDER BY table_schema, table_name, ordinal_position;
  </code></pre>
  
  <p>And converted every table just in case</p>
  
  <pre><code>ALTER TABLE `my_database`.`table_name` DEFAULT COLLATE utf8_unicode_ci;      
  ALTER TABLE `my_database`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;         
  </code></pre></li>
  <li><p>My database collation settings are in <code>utf8_unicode_ci</code>.</p>
  
  <p>charsets are</p>
  
  <pre>
  mysql> show variables like 'char%';
  +--------------------------+----------------------------+
  | Variable_name            | Value                      |
  +--------------------------+----------------------------+
  | character_set_client     | utf8                       |
  | character_set_connection | utf8                       |
  | character_set_database   | utf8                       |
  | character_set_filesystem | binary                     |
  | character_set_results    | utf8                       |
  | character_set_server     | utf8                       |
  | character_set_system     | utf8                       |
  | character_sets_dir       | /usr/share/mysql/charsets/ |
  +--------------------------+----------------------------+
  8 rows in set (0.02 sec)</pre>
  
  <p>collations are</p>
  
  <pre>mysql> show variables like 'colla%';
  +----------------------+-----------------+
  | Variable_name        | Value           |
  +----------------------+-----------------+
  | collation_connection | utf8_unicode_ci |
  | collation_database   | utf8_unicode_ci |
  | collation_server     | utf8_unicode_ci |
  +----------------------+-----------------+
  3 rows in set (0.00 sec)</pre></li>
  <li><p>the error is triggered whether I call the stored procedure via web browser or via mysql bash client. just in case my ubuntu/linux locale settings are:</p>
  
  <pre>$ locale
  LANG=es_ES.UTF-8
  LANGUAGE=es_ES.UTF-8
  LC_CTYPE=es_ES.UTF-8
  LC_NUMERIC="es_ES.UTF-8"
  LC_TIME="es_ES.UTF-8"
  LC_COLLATE=es_ES.UTF-8
  LC_MONETARY="es_ES.UTF-8"
  LC_MESSAGES=es_ES.UTF-8
  LC_PAPER="es_ES.UTF-8"
  LC_NAME="es_ES.UTF-8"
  LC_ADDRESS="es_ES.UTF-8"
  LC_TELEPHONE="es_ES.UTF-8"
  LC_MEASUREMENT="es_ES.UTF-8"
  LC_IDENTIFICATION="es_ES.UTF-8"
  LC_ALL=</pre></li>
  </ol>
  
  <p>The only way I've been able to solve this issue is using <code>convert</code> inside each query that causes the error (or using <code>COLLATE</code> inside the query), but the problem is that there are a lot of quite complex stored procedures so it's hard to identify the "bad" queries and takes a lot of time.</p>
  
  <p>I guess that somehow the variables passed to the stored procedure from my system (ubuntu : mysql client, browser), are being sent in utf8_general_ci, so it makes conflict with ut8_unicode_ci from my database. </p>
  
  <p>It seems that the os is working with utf8_general_ci even though the mysql connection is set to utf_unicode_ci.</p>