Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+4 votes
71 views
in Q2A Core by

Hi, I already changed the collation but I still have this error:

[22-Dec-2022 18:36:15 UTC] PHP Question2Answer MySQL query error 1253: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4' - Query: SELECT qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type, 1) AS `basetype`, INSTR(qa_posts.type, '_HIDDEN')>0 AS `hidden`, INSTR(qa_posts.type, '_QUEUED')>0 AS `queued`, qa_posts.acount, qa_posts.selchildid, qa_posts.closedbyid, qa_posts.upvotes, qa_posts.downvotes, qa_posts.netvotes, qa_posts.views, qa_posts.hotness, qa_posts.flagcount, qa_posts.title, qa_posts.tags, UNIX_TIMESTAMP(qa_posts.created) AS `created`, qa_posts.name, qa_categories.title AS `categoryname`, qa_categories.backpath AS `categorybackpath`, CONCAT_WS(',', qa_posts.catidpath1, qa_posts.catidpath2, qa_posts.catidpath3, qa_posts.categoryid) AS `categoryids`, qa_uservotes.vote AS `uservote`, qa_uservotes.flag AS `userflag`, qa_userfavorites.entityid<=>qa_posts.postid AS `userfavoriteq`, qa_posts.userid, qa_posts.cookieid, qa_posts.createip AS `createip`, qa_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 qa_posts LEFT JOIN qa_categories ON qa_categories.categoryid=qa_posts.categoryid LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid='1' LEFT JOIN qa_userfavorites ON qa_posts.postid=qa_userfavorites.entityid AND qa_userfavorites.userid='1' AND qa_userfavorites.entitytype='Q' LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN (SELECT postid FROM qa_posttags WHERE wordid=(SELECT wordid FROM qa_words WHERE word='صادرة' AND word='صادرة' COLLATE utf8_bin LIMIT 1) ORDER BY postcreated DESC LIMIT 0,20) y ON qa_posts.postid=y.postid

ALTER TABLE `qa_words` CHANGE `word` `word` VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `qa_users` CHANGE `handle` `handle` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `qa_messages` CHANGE `content` `content` VARCHAR(12000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

This happen only when I click on a tag

What I'm missing?
 

1 Answer

0 votes
by
 
Best answer

Changing these two lines from utf8_bin to utf8mb4_bin, seems solve the issue but idk if it will cause an problem:

    $selectspec['source'] .= " JOIN (SELECT postid FROM ^posttags WHERE wordid=(SELECT wordid FROM ^words WHERE word=$ AND word=$ COLLATE utf8mb4_bin LIMIT 1) ORDER BY postcreated DESC LIMIT #,#) y ON ^posts.postid=y.postid";
 

        'source' => '^words WHERE word=$ AND word=$ COLLATE utf8mb4_bin',

file: selects.php

...