Smartsearch: collation issue when searching in back-end index by infograf768 · Pull Request #8307 · joomla/joomla-cms

infograf768 · 2015-11-07T07:59:58Z

This solves #8298

Create an article with title in Hebrew
ביו
then purge and run smartsearch indexer (I used snowball as indexer)
When done, enter the title in the search box (still in the indexer page):

we get

500 Illegal mix of collations for operation 'like' SQL=SELECT COUNT() FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%' Illegal mix of collations for operation 'like' SQL=SELECT l.,t.title AS t_title FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%' LIMIT 0, 20 Illegal mix of collations for operation 'like' SQL=SELECT COUNT() FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%' Illegal mix of collations for operation 'like' SQL=SELECT COUNT() FROM `#__finder_links` AS l INNER JOIN `#__finder_types` AS t ON t.id = l.type_id WHERE l.type_id = 4 AND l.title LIKE '%ביו%' OR l.url LIKE '%ביו%' OR l.indexdate LIKE '%ביו%'

the issue comes from the fact that the search query includes the indexdate while it is anyway useless here.
See: http://stackoverflow.com/questions/18629094/illegal-mix-of-collations-for-operation-like-while-searching-with-ignited-data for explanation.

After getting the 500, logout, login again and patch.
try again.
You should get :

pe7er · 2015-11-07T09:00:45Z

I have tested this item ✅ successfully on bf3c49e

I was able to reproduce the error. This PR fixes it. Thanks JM!

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8307.}

dgrammatiko · 2015-11-07T09:21:16Z

I have tested this item ✅ successfully on bf3c49e

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8307.}

infograf768 · 2015-11-07T09:27:37Z

2 good test: RTC

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8307.}

wilsonge · 2015-11-07T09:58:39Z

@infograf768 if you just remove the like rather than the whole query does it work? Iirc you can't use LIKE for a database date time object. And it would still allow filtering by date for those who want it (sorry travelling on a train so can't test myself to see if it would work)

infograf768 · 2015-11-07T10:08:22Z

@wilsonge
We just do not need that query there. No need to go further imho.

infograf768 · 2015-11-07T10:09:52Z

I meant we do not need ' OR l.indexdate LIKE ' . $search which is only part of the query

infograf768 · 2015-11-07T10:11:57Z

oh, you mean just letting `' OR l.indexdate'). Hmm...
I do not see how anyone would need that in the back-end search...

wilsonge · 2015-11-07T10:40:31Z

I mean like OR l.indexdate = $search yeah

I'm not sure either. But if there is a use case at least it would still work?

infograf768 · 2015-11-07T10:53:17Z

If someone hacks core admin it is his/her problem

wilsonge · 2015-11-07T13:13:33Z

It's not a hack! This is a thing that can be used at the moment?

infograf768 · 2015-11-07T17:43:18Z

The only way to keep a search by date is to preg_match $search to make sure it does not contain unicode characters.
One can indeed search by 2015-11-07 in that case and this whatever the date format for the language used in back-end (in French the column would show 7/11/15 but it would not fit the indexdate in the column.
I doubt anybody would have ever used that in the Indexer page...
Anyway, this would work

        // Check the search phrase.
        if ($this->getState('filter.search') != '')
        {
            $search = $db->quote('%' . str_replace(' ', '%', $db->escape(trim($this->getState('filter.search')), true) . '%'));

            // Do not filter by indexdate if $search contains non-ascii characters
            if (preg_match('/[^\x00-\x7F]/', $search))
            {
                $query->where('l.title LIKE ' . $search . ' OR l.url LIKE ' . $search);
            }
            else
            {
                $query->where('l.title LIKE ' . $search . ' OR l.url LIKE ' . $search . ' OR l.indexdate LIKE  ' . $search);
            }
        }

Would you be satisfied with this?

joomla-cms-bot · 2015-11-07T17:48:02Z

This PR has received new commits.

CC: @DGT41, @pe7er

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8307.}

fontanil · 2015-11-08T09:17:03Z

I have tested this item ✅ successfully on 00f2a62

Tested on 3.5.0 beta1: successfull on date and unicode. Thanks

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8307.}

infograf768 · 2015-11-09T06:45:29Z

@DGT41, @pe7er, @wilsonge
One test more please to get this in.

waader · 2015-11-09T07:41:02Z

I have tested this item ✅ successfully on 00f2a62

Thanks,infograf768!

_{This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8307.}

Smartsearch: collation issue when searching in back-end index

ertinet · 2015-11-09T09:58:09Z

Thank you all for your assistance
We've tested it and it work perfectly

Thanks and Regards
Rose ERTINET

Search does not need indexdate

bf3c49e

joomla-cms-bot added the PR-staging label Nov 7, 2015

joomla-cms-bot added the RTC This Pull Request is Ready To Commit label Nov 7, 2015

infograf768 mentioned this pull request Nov 7, 2015

Smartsearch: collation issue when searching in back-end #8298

Closed

zero-24 added this to the Joomla! 3.5.0 milestone Nov 7, 2015

Allowing search by date

00f2a62

rdeutz added a commit that referenced this pull request Nov 9, 2015

Merge pull request #8307 from infograf768/finder_indexdate

1d3a872

Smartsearch: collation issue when searching in back-end index

rdeutz merged commit 1d3a872 into joomla:staging Nov 9, 2015

joomla-cms-bot removed the RTC This Pull Request is Ready To Commit label Nov 9, 2015

infograf768 deleted the finder_indexdate branch November 9, 2015 12:03

Uh oh!

Conversation

infograf768 commented Nov 7, 2015

Uh oh!

pe7er commented Nov 7, 2015

Uh oh!

dgrammatiko commented Nov 7, 2015

Uh oh!

infograf768 commented Nov 7, 2015

Uh oh!

wilsonge commented Nov 7, 2015

Uh oh!

infograf768 commented Nov 7, 2015

Uh oh!

infograf768 commented Nov 7, 2015

Uh oh!

infograf768 commented Nov 7, 2015

Uh oh!

wilsonge commented Nov 7, 2015

Uh oh!

infograf768 commented Nov 7, 2015

Uh oh!

wilsonge commented Nov 7, 2015

Uh oh!

infograf768 commented Nov 7, 2015

Uh oh!

joomla-cms-bot commented Nov 7, 2015

Uh oh!

fontanil commented Nov 8, 2015

Uh oh!

infograf768 commented Nov 9, 2015

Uh oh!

waader commented Nov 9, 2015

Uh oh!

ertinet commented Nov 9, 2015

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

10 participants