27

Alright, I give up. I'm 2 days into this error and I need help.

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.

The Question (also, asked below in context):

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?

The Problem: Autoresponder.find_by(keyword: '') fails with the following error:

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

Autoresponder is a model that has the attribute keyword

I read that I needed to specify my collation. So I tested out the following code:

Autoresponder.where('keyword collate utf8mb4_unicode_ci = ?', '')

and got the following error:

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 = '')

All that did was change the collation from IMPLICIT to EXPLICIT.

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:

SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword collate utf8mb4_unicode_ci = '');

SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword = ' ');

AND it works! The queries ran without errors. I also ran mysql and was able to run the query there too. But I noticed something when I pasted the query into the mysql 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:

SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword collate utf8mb4_unicode_ci ='\U+1F615');

This query works.

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?

I thought that it might be coming from ActiveRecord, but running ActiveRecord::Base.connection.collation in rails console returns utf8mb4_unicode_ci

Here are my db character encoding and collation variables (and the query that retrieved them).

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

Here is the create syntax for the Autorsponders table:

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;

Context: Rails 4.0.13, Mysql version 5.6.22-1+deb.sury.org~precise+1-log

Here are some blog posts and SO articles that I have read so far: https://mathiasbynens.be/notes/mysql-utf8mb4

http://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/

Is "SET CHARACTER SET utf8" necessary?

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

Not case sensitive search with active record

https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_collation_server

All of this led me to create this meme:

enter image description here

Sincerely,

An exhausted fellow developer.

Thanks.

Community
  • 1
  • 1
Jared Menard
  • 2,654
  • 1
  • 21
  • 22
  • 2
    The error message implies that MySQL is claiming that the _client_ is feeding '' as a utf8 character. (It should be claimed to be utf8mb4.) I don't know the ins and outs of Ruby and ActiveRecord, but that is where I would look. – Rick James Sep 13 '15 at 04:40
  • Thanks for that. I've stopped working on this issue for the time being. I'll probably come back to it in a few weeks. If I ever figure it out I will document the answer here. The weird thing is that it now works in development (OS X) but not in staging (Ubuntu) – Jared Menard Sep 15 '15 at 00:22
  • I ended up "fixing" this issue by using a blob instead of text. – Jared Menard Feb 11 '16 at 17:54
  • 1
    Also, we now use postgres, which supported 4-byte chars out of the box. – Jared Menard Jan 09 '17 at 16:16

5 Answers5

20

I met similar problem and solved finally. At first, my MySQL conf is：

character-set-server = utf8
collation-server     = utf8_general_ci

One day, i found that emoji could be saved correctly only with utf8mb4, so i change the specified column's character set and collation like this:

  `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

By now, everything still fine, data could be saved and display correctly by java web application.

but when i query data like

SELECT * FROM table_name WHERE nickname LIKE '%%';

error raises.

Finally i changed mysql conf to

character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

and everything goes fine.

Make sure also that any call of SET NAMES utf8; is removed or replaced by SET NAMES utf8mb4

Here is a screenshot of mysql client, notice the nickname attribute

giosh94mhz
  • 2,908
  • 14
  • 25
toien
  • 288
  • 3
  • 11
  • If you have a ruby on rails app, you can change the config by adding the following in your database.yml ```encoding: utf8mb4 charset: utf8mb4 collation: utf8mb4_unicode_ci``` Make sure it's in 3 lines (stackoverflow breaks the lines) – ajimix Feb 16 '19 at 02:59
  • Please show us the entire file – Florent Oct 30 '21 at 10:49
6

I had the same error i added the collate after the where statement

SELECT *  FROM chat_words where source ='forum';

throw the Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

I later chaged to

SELECT *  FROM chat_words where source collate utf8mb4_unicode_ci ='forum';

this run free of errors

mumbasa
  • 632
  • 7
  • 11
1

I have solved this problem from phpMyAdmin by changing the collation for columns to utf8mb4_unicode_ci.

Stephen Kennedy
  • 20,585
  • 22
  • 95
  • 108
osama ibrahem
  • 11
  • 1
1

I have the same issue when I duplicate my database to other server. I have change my collation to utf8mb4_general_ci and it seems to be working fine.

Amit Sharma
  • 1,775
  • 3
  • 11
  • 20
1

I ran now into the same issue and found out that Debian fixed the collation in the config (/etc/mysql/mariadb.conf.d/50-server.cnf)

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

After changing the collation here the issue is gone.

Markus Krenn
  • 11
  • 1