Issues when switching db_connect from UTF8 to UTF8mb4

Question

To allow emojis and other characters that come with ut8mb4 in q2a, there are some hints what code has to be changed:

- https://www.question2answer.org/qa/28769/why-isnt-emoji-supported-in-q2a
- https://www.question2answer.org/qa/54525/is-there-a-way-to-get-unicode-smiley-work-with-q2a

However, when changing the DB connection in qa-db.php from "utf8" to "utf8mb4" q2a runs into this error:

PHP Question2Answer MySQL query error 1253: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4' - Query: SELECT example.postid, example.categoryid, example.type, LEFT(example.type, 1) AS basetype, INSTR(example.type, '_HIDDEN')>0 AS hidden, INSTR(example.type, '_QUEUED')>0 AS queued, example.acount, example.selchildid, example.closedbyid, example.upvotes, example.downvotes, example.netvotes, example.views, example.hotness, example.flagcount, example.title, example.tags, UNIX_TIMESTAMP(example.created) AS created, example.name, example.userid, example.cookieid, example.createip AS createip, example_userpoints.points, qa_users.flags, qa_users.level, qa_users.email AS email, qa_users.handle AS handle, BINARY qa_users.avatarblobid AS avatarblobid, qa_users.avatarwidth, qa_users.avatarheight FROM example LEFT JOIN qa_users ON example.userid=qa_users.userid LEFT JOIN example_userpoints ON example.userid=example_userpoints.userid JOIN (SELECT postid FROM example_posttags WHERE wordid=(SELECT wordid FROM example_words WHERE word='cracken' AND word='cracken' COLLATE utf8_bin LIMIT 1) ORDER BY postcreated DESC LIMIT 0,20) y ON example.postid=y.postid

"COLLATE utf8_bin" is used one time in db/install.php and two times db/selects.php.

What would be a smart way to overcome this problem?

Comments

Change those collates too :)

---

Have you solve it?

---

Yes, it was a couple of years back. I had to do for all those tables and columns.