Need some help with DB collation woe
Hi, one of my Tiki 18.8 has stopped to search... After login in as Admin, I could see the whole error message. Much to my astonishment it was
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' Die Abfrage war: SELECT DISTINCT c.`title` AS name, LEFT(c.`data`, 240) AS data, p.`hits` AS hits, c.`commentDate` AS lastModif, CONCAT(p.`pageName`,': ',c.`title`) AS pageName,outputType ,p.`pageName` AS id1,c.`threadId` AS id2, MATCH(c.`title`,c.`data`) AGAINST ('Kämpfer' IN BOOLEAN MODE) AS relevance FROM `tiki_comments` c, `tiki_pages` p left join `tiki_output` on `tiki_output`.`entityId` = p.`pageName` WHERE c.`objectType` = 'wiki page' AND p.`pageName`=c.`object` AND MATCH(c.`title`,c.`data`) AGAINST ('Kämpfer' IN BOOLEAN MODE) ORDER BY relevance desc, p.`hits` The built query was likely: SELECT DISTINCT c.`title` AS name, LEFT(c.`data`, 240) AS data, p.`hits` AS hits, c.`commentDate` AS lastModif, CONCAT(p.`pageName`,': ',c.`title`) AS pageName,outputType ,p.`pageName` AS id1,c.`threadId` AS id2, MATCH(c.`title`,c.`data`) AGAINST ('Kämpfer' IN BOOLEAN MODE) AS relevance FROM `tiki_comments` c, `tiki_pages` p left join `tiki_output` on `tiki_output`.`entityId` = p.`pageName` WHERE c.`objectType` = 'wiki page' AND p.`pageName`=c.`object` AND MATCH(c.`title`,c.`data`) AGAINST ('Kämpfer' IN BOOLEAN MODE) ORDER BY relevance desc, p.`hits`
This particular Tiki has data from very old days, migrated lots of times. So far no problems with the DB.
When looking deeper I found that for reasons unknown search was set to Lucene, which I do not recall ever activating. Deactivating it, and rebuilding the index (successful) did not help. I had the suspicion it could be related to PHP 7.3, so I downgraded that to 7.2, on which another Tiki 18.8 runs, including search. To no avail.
A look at my backups showed me that a discrepancy between the default
CREATE DATABASE IF NOT EXISTS `MYDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
and many tables like
CREATE TABLE `galaxia_activities` ( `activityId` int(14) NOT NULL, `name` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL, `normalized_name` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL, ...
exist. And have existed for looong. So why is this causing problems now?
And how can I rectify this?
Thanks
hman