CODEWITHSUNDEEP
mysqlknex.jsadonis.js
Hkm Sadek
Hkm Sadek

Reputation: 3209

COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

I am trying to add emoji like 😋 in my application. In order to make it work, I have to use charset : 'utf8mb4' in database connection. But then my other search query doesn't work and throughs error like this

select id, full_name, profile_pic from users where school_id = 1 and exists (select * from groupmembers where (group_id = '110') and (users.id = groupmembers.user_id)) and full_name like '%d%' COLLATE utf8_bin - ER_COLLATION_CHARSET_MISMATCH: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4

How can I make both works together? I am using adonis.js framework and it uses knex query/

Upvotes: 2

Views: 9488

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562801

Collations are specific to a character set. If your character set is utf8mb4, then you could use collation utf8mb4_bin but not utf8_bin.

This will become a bit more confusing someday, because MySQL has the intention to change the name of utf8mb4 to utf8. Cf. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

You can check all the collations allowed by this query:

select collation_name 
from information_schema.collation_character_set_applicability 
where character_set_name = @@character_set_connection;

Upvotes: 4

Related Questions