0

I have a collation error with MySQL, specifically it is the following:

OperationalError: (1267, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='")

After checking, I noticed that it is related to certain emojis, mostly smileys. I've checked my MySQL database and it is by default using utf8mb4 charset as shown:

+------------+---------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                   |
+------------+---------------------------------------------------------------------------------------------------+
| Dictionary | CREATE DATABASE `Dictionary` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+------------+---------------------------------------------------------------------------------------------------+

My MySQL settings also indicate that everything needed is set to utf8mb4:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

I am using Peewee (the Python module) to make my queries and the following line is the one that is causing the collation error

SingleWords.get(SingleWords.word == lowercase)

It is not much of a problem for me if I can't insert certain emojis, but I would still like to if possible. I have no idea why this is happening, any thoughts?

Btara Truhandarien
  • 1
  • 1
  • 3
  • possible duplicate of [How to solve - Illegal mix of collations in mysql?](http://stackoverflow.com/questions/3029321/how-to-solve-illegal-mix-of-collations-in-mysql) – Eduardo Feb 21 '14 at 11:43
  • I tried everything on that, still no go. I suppose it's my bad because of ambiguity on my question. Now I edited it to be more precise. I know peewee by default creates tables with utf8 as the default charset, but which utf8 I'm not sure i.e whether it is simply utf8 or utf8mb4, which is the one I'm using Edit: I just checked with the following mysql command: SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_name = "singlewords" AND column_name = "word"; and it says utf8mb4 – Btara Truhandarien Feb 21 '14 at 13:40

1 Answers1

0

try this

  SELECT * COLLATE latin1_general_ci FROM table;

this will select all with collate latin1_general_ci

echo_Me
  • 37,078
  • 5
  • 58
  • 78