?
avatar french150
french150
4 Apr 2016

Steps to reproduce the issue

I search an article by tiping a word and i click on "search" button.

Expected result

Usually i see the list of the news containing that word as result of research

Actual result

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

System information (as much as possible)

Joomla 3.5.0
DB 5.5.46 version
PHP version 5.6.15
Web server APACHE

Additional comments

avatar french150 french150 - open - 4 Apr 2016
avatar brianteeman brianteeman - change - 4 Apr 2016
Category Search
avatar richard67
richard67 - comment - 4 Apr 2016

@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.

avatar richard67
richard67 - comment - 4 Apr 2016

@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.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9722.

avatar french150
french150 - comment - 4 Apr 2016

I don't use any 3rd party search extensions. I use only the Joomla search. So what do you suggest me?

avatar richard67
richard67 - comment - 4 Apr 2016

Hmm, then I have no idea.

avatar mbabker
mbabker - comment - 4 Apr 2016

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.

avatar french150
french150 - comment - 4 Apr 2016

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

avatar brianteeman brianteeman - close - 4 Apr 2016
avatar brianteeman brianteeman - close - 4 Apr 2016
avatar roland-d roland-d - change - 4 Apr 2016
Status New Closed
Closed_Date 0000-00-00 00:00:00 2016-04-04 16:17:52
Closed_By roland-d
avatar roland-d roland-d - close - 4 Apr 2016
avatar brianteeman brianteeman - close - 4 Apr 2016
avatar brianteeman brianteeman - change - 22 Apr 2016
Labels Added: ?
avatar brianteeman brianteeman - change - 22 Apr 2016
Labels Removed: ?
avatar brianteeman brianteeman - change - 22 Apr 2016
Labels Added: ?

Add a Comment

Login with GitHub to post a comment