Unknown column 'Relevance' in 'order clause

[keat]

Back to this troublesome V3 to 6.1.5 site.

I've noticed the first search for a product code comes back empty.

 

with an error in debug

[1] 1: Unknown column 'Relevance' in 'order clause' [ERROR - CACHE WRITE]

 

[16] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.000586986541748 sec) [CACHE WRITE]

 

Any subsequent searches for that product_code are OK.

Any pointers ?

 

[Noodleman]

try this.

https://github.com/cubecart/v6/issues/1330

 

 

[keat]

The git hub post refers to this being fixed in 6.1.2 or 6.1.3, this site is 6.1.5. ??

catalogue.class is slightly different than the one referred to in this github post, so I dare not start modifying it willy nilly.

 

https://github.com/cubecart/v6/commit/55e2b4f2ee462a146c094670d1d04ac9cdc5d0ab

 

 

[Noodleman]

yup, but it's worth testing "just in case" as it was throwing the same, or similar error

[keat]

With it being different, I wouldn't know which lines to add or take away.

I also have a live 6.1.5 site and get a similar issue, only the error in debug is a little different.

This live site (IIRC) is untouched virgin site.

[13] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1760] \"SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE \'[[::]]\' OR I.description RLIKE \'[[::]]\' OR I.product_code RLIKE \'[[::]]\') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'Relevance\' in \'order clause\'','1487172183'); -- (0.00065803527832 sec) [ERROR - NOT CACHED]
[14] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1760] \"SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE \'[[::]]\' OR I.description RLIKE \'[[::]]\' OR I.product_code RLIKE \'[[::]]\') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'Relevance\' in \'order clause\'','1487172183'); -- (0.00065803527832 sec) [ERROR - CACHE WRITE]

Live site and troublesome v3 to v6 site are on different servers.

[bsmither]

The query has regular expression syntax (RLIKE), but is using a sorter that should have been rejected once CubeCart entered this phase of searching. Nor is the query actually looking for anything ([[::]]).

I have not seen this in CC614 (have not yet installed CC615).

So, if this site is accessing an existing database, that database may have a code snippet that is not working correctly with this installation.

[keat]

I think it might be related to the length of the search string ??

eg, if I search for product_code R16 (it does exist), nothing comes back

If I search for R16-BUTT (it also exists) then results come back.

Also search for AT9, no results, AT97 works.

Could 3 carachters be to few

 

www.crimpconnectors.co.uk is the live site.

Debugging is enabled if anyone wants to see.

Search TR1 and then search TR10

[bsmither]

Searching is in three phases: Relevance, RLIKE, then LIKE. The next phase is employed if no results are found.

Relevance searches the FULLTEXT index of which, by default, the minimum number of letters to use as a search term is four.

But this does not explain why the search term is getting lost when doing the RLIKE phase.

Unless this site is accessing an existing database, which that database may have a code snippet that is not working correctly with this installation.

[keat]

The live site crimp connectors has no snippets or mods. A few plugs like PayPal and all in one shipping. That's it.

Is it possible to reduce that search limitation to 3 characters. 

[bsmither]

I believe that is a database server configuration setting - not changeable by issuing a query.

[keat]

My 6.0.10 site doesn't have this limitation, so I'm guessing therefore that this is something to do with 6.1.5.

I never noticed if 6.1.2 suffered the same issue.

 

[bsmither]

In phpMyAdmin, run this query:

SHOW VARIABLES LIKE 'ft_min_word_len'

If the answer is not a number, then CubeCart will use four.

[Dirty Butter]

I'm on 6.1.5 and can search on our plushcatalog successfully for TY, but my Search is NOT stock. My server ft_min_word_len = 3.

[havenswift-hosting]

9 hours ago, bsmither said:

In phpMyAdmin, run this query:

SHOW VARIABLES LIKE 'ft_min_word_len'

If the answer is not a number, then CubeCart will use four.

CubeCart will use 4 as that is the default MySQL setting

8 hours ago, Dirty Butter said:

My server ft_min_word_len = 3.

We set this one character lower on our servers

[keat]

My server ft_min_word_len = 4.

 

However

I've just created a new install of 6.0.10, and added 2 x test products. TP1 and TP10.

I can search and find both.

 

I then updated this to 6.1.5 and get the following error when searching TP1

 

[10] SELECT SQL_CALC_FOUND_ROWS `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'TP1' LIMIT 1; -- (0.000303983688354 sec) [NOT CACHED]
[11] SELECT FOUND_ROWS() as Count; -- (8.98838043213E-5 sec) [NOT CACHED]
[12] UPDATE `CubeCart_search` SET `hits` = '5' WHERE CubeCart_search.id = '1'; -- (0.0791778564453 sec) [NOT CACHED]
[13] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000940084457397 sec) [CACHE WRITE]
[14] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000609874725342 sec) [CACHE WRITE]
[15] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.00070595741272 sec) [CACHE WRITE]
[16] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.00115895271301 sec) [CACHE WRITE]

[1] 1: Unknown column 'Relevance' in 'order clause' [ERROR - CACHE WRITE]

 

This is a virgin site, absolutely no mods, snippets or plugins.

The only changes from stock, are the changes to ini.inc.php to get around the PHP 5.4 limilation and config.class from 6.0.12

Something must have changed in 6.1 that wasn't there in 6.0

 

 

Here is the search from a working 6.0.10

[25] SELECT `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'UG3' ; -- (0.0163061618805 sec) [CACHE WRITE]
[26] UPDATE `CubeCart_search` SET `hits` = '1' WHERE CubeCart_search.id = '44901'; -- (0.000336885452271 sec) [NOT CACHED]
[27] SHOW COLUMNS FROM CubeCart_customer_membership; -- (0.000882148742676 sec) [CACHE WRITE]
[28] SELECT `group_id` FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000330209732056 sec) [CACHE WRITE]
[29] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000654935836792 sec) [CACHE WRITE]
[30] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000591039657593 sec) [CACHE WRITE]
[31] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') LIMIT 12 OFFSET 0 -- (0.141498088837 sec) [CACHE WRITE]
[32] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.13321018219 sec) [CACHE WRITE]
[33] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'category_product_options' LIMIT 1; -- (0.000521183013916 sec) [CACHE WRITE]
[34] SELECT FOUND_ROWS() as Count; -- (0.000123023986816 sec) [NOT CACHED]
 

[keat]

It seems that it's something in catalogue.class.php but what.

If I restore catalogue.class.php from V6.0.10 in to the 6.1.5 classes folder, the search works.

If I resore catalogue.class.php back to it's original 6.1.5 version, it stops working again.

 

I've had a little look and tried to compare the two, but it might as well be encrypted.

[Dirty Butter]

The Search that Bsmither built for me uses RLIKE instead of Fulltext. Looks like your 6.0.10 does as well. I don't use Relevance, either.

When I get a chance I'll run a file compare of the two catalog.class.php files.

 

[keat]

I've definately narrowed it down to a change in 6.1.

I'm trying to compare 6.0.12 and 6.1.0, but there are sections which are so different, that i've no chance of figuring it out.

 

Any thoughts where I might comment out the step 'Order by Relevance',

[bsmither]

You can try forcing CubeCart to go straight to the second method. In /classes/catalogue.class.php, near line 1571:

From:
public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {

To:
public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'RLIKE') {

However, as seen earlier, it appears that the search gets to be using RLIKE, but is missing search terms. So, if this edit doesn't smooth that out, then it must be the case that a code snippet is interfering.

[keat]

That seems to have fixed the issue.

However, having said that, I'd already tested with catalogue.class from 6.0.12, and the search worked.

The string in catalogue.class is also:       public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {

So whilst changing the entry from 'fulltext' to 'RLIKE' appears to have solved the issue, it must be something else that's actually causing it.

[Dirty Butter]

Looking at the file compare of 6.0.11 and 6.15:

This line in 6.1.5       $query = $q2.' '.$order_string.' '.$limit;

Is like this is 6.0.11   $query = $q2.' '.$limit;

Could THAT be it?

[keat]

I restored 'fulltext' and then removed '.$order_string.'  (around line 1816), and this also seems to have fixed the issue.

 

So I guess we now need to understand what it does and why is it in there ?

 

And just as importantly, why is it only me who has this problem ?

 

[bsmither]

It was added because otherwise, there would be no sorting at all. But I think the Relevance is coming from the sorter choice on the Advanced Search page.

This part of a query, as it appears in CubeCart's debug section:
(I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]')

is supposed to look like this:
(I.name RLIKE '[[:<:]]search_term[[:>:]]' OR I.description RLIKE '[[:<:]]search_term[[:>:]]' OR I.product_code RLIKE '[[:<:]]search_term[[:>:]]')

But this part:
<:]]search_term[[:>
is getting stripped out.

The "tag" (surrounded by angles) is getting stripped off in Debug->debugSQL(). (I've argued for removing that, but it is still there.) So, the search term in the query should be getting to the database, it is just that we will not see this in the debug section. We can probably drop the examination of whether the search term, itself, is at fault.

The code is supposed to not use relevance if the search method starts at 'fulltext', and then falls through to the next method. If 'Relevance', the sort is supposed to switch to using the admin-set default sort parameters.

[keat]

I understood the explanation, but not the code, is there a definitive fix for this.

[bsandall]

I'll look into the $order_string issue, but I've also just created a PR to allow the admin to choose the default search mode, a setting that probably should have been configurable all along. This will allow you to swap between fulltext, like, or rlike from the admin panel.

See my fix here.