I search an article by tiping a word and i click on "search" button.
Usually i see the list of the news containing that word as result of research
I get error 1253. Here the message:
COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4' SQL=SELECT a.title AS title, a.metadesc, a.metakey, a.catid, a.id, a.state, a.created AS created, a.images AS picture, c.title AS category, u.name AS author,CONCAT(a.introtext,a.fulltext) AS text, CASE WHEN CHAR_LENGTH(a.alias) != 0 THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) != 0 THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as catslug, CASE WHEN a.state = 2 THEN CONCAT_WS(' | ', 'Archiviato', c.title) ELSE CONCAT_WS(' | ', 'Articoli', c.title) END as section, "2" AS browsernav FROM #__content AS a INNER JOIN #__categories AS c ON c.id = a.catid AND c.access IN (1,1,3,5,10,11,15,16,17,25,26,27,28,30) LEFT JOIN #__users AS u ON u.id = a.created_by WHERE ((a.title LIKE '%paghe%' COLLATE utf8_bin OR a.introtext LIKE '%paghe%' COLLATE utf8_bin OR a.fulltext LIKE '%paghe%' COLLATE utf8_bin OR a.metakey LIKE '%paghe%' COLLATE utf8_bin OR a.metadesc LIKE '%paghe%' COLLATE utf8_bin)) AND c.published = 1 AND a.access IN (1,1,3,5,10,11,15,16,17,25,26,27,28,30) AND (a.state = 1 OR a.state = 2) AND a.catid NOT IN (2,9,19,23) AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-04-04 10:23:23') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-04-04 10:23:23') GROUP BY a.id, a.title, a.metadesc, a.metakey, a.created, a.introtext, a.fulltext, c.title, a.alias, c.alias, c.id, u.name ORDER BY a.created DESC
Joomla 3.5.0
DB 5.5.46 version
PHP version 5.6.15
Web server APACHE
Category | ⇒ | Search |
@french150 P.S.: It is the "COLLATE utf8_bin" in these statements which makes it fail ... and such statements with "COLLATE" I did not find anywhere in the Joomla! Core's php files, as I mentione above. IF it is a 3rd party extension you can fix that by changing "COLLATE utf8_bin" to "COLLATE utf8mb4_bin" in the file where this extension uses that, in case if the extension does not already offer an update for that.
So I am almost sure it is some 3rd party extension you use, also because I cannot replicate the problem here on my 3.5.0., which has been converted to utf8mb4, too, as yours obviosly has been (Jooomla! does that on update when db server supports this).
But as I said, I might be wrong, so if @brianteeman knows any search expert he could maybe trigger him to check that, too.
I don't use any 3rd party search extensions. I use only the Joomla search. So what do you suggest me?
Hmm, then I have no idea.
Joomla core doesn't have anything appending COLLATE to any query strings. Also, comparing the query you've posted here with either com_content search plugin (Smart Search or "legacy" Search) they aren't the same (the core Search plugin doesn't have the join to the users table and neither plugin has a a.images AS picture
element of the SELECT query). So I'm inclined to say there is a third party extension involved here. Attaching the system info export would help to identify that.
You are right! I founf the 3rd party extension that caused it. I updated it and all works now.
Thanks for your replies.
Best regards
Status | New | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-04-04 16:17:52 |
Closed_By | ⇒ | roland-d |
Labels |
Added:
?
|
Labels |
Removed:
?
|
Labels |
Added:
?
|
@french150 Do you use any 3rd party search extensions? The SQL statement in the error message looks very much like this. At least I cannot find in Joomla! Core's php where such a statement is built or used. But maybe I am wrong.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9722.