nomem Posted November 2, 2016 Share Posted November 2, 2016 Woke up this morning and found this erroy in my system error log. File: [catalogue.class.php] Line: [1726] "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 fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%') LIMIT 9 OFFSET 0 " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%')' at line 1 Any thoughts? Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 2, 2016 Share Posted November 2, 2016 I am finding flaws in the construction of the query. There is an updated procedure in searching the catalogue with, now, three methods: fulltext, RLIKE, and LIKE. Having added code to construct a LIKE syntax, passively dealing with fulltext now fails. An issue will get logged in the Github. Also, line 1726 is not where the query would fault. Have you made edits to /classes/catalogue.class.php? Quote Link to comment Share on other sites More sharing options...
nomem Posted November 2, 2016 Author Share Posted November 2, 2016 Just the changes for the Prev::Next coding Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 2, 2016 Share Posted November 2, 2016 I understand. Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 2, 2016 Share Posted November 2, 2016 Please read: https://github.com/cubecart/v6/issues/1330 Perform an experiment by making the edit, but for you, your line numbers will be off by 12 or 13. Quote Link to comment Share on other sites More sharing options...
nomem Posted November 2, 2016 Author Share Posted November 2, 2016 I made the change in the code and have no new errors. But my search is not working from the search box. When I type in coke bottle it comes back none found error. When I search with advanced search all my coke bottles are found I put the code back to original and get the same as with new code Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 3, 2016 Share Posted November 3, 2016 Actually, I have no answer for why coke bottle is not found. I see no reason why the queries wouldn't work. CubeCart's first attempt is using the fulltext index - which the database server engine processes are hard to grok. But if no results are found, then CubeCart uses RLIKE - looking for whole words. If no results are found, then CubeCart uses LIKE - looking for the sequence of characters that may be found in larger words, per word. (Results such as: cokeheads bottlenecks.) The edit specified in the Github is to solve a logic problem when the lengths of all the search word(s) are less than the fulltext minimum (generally four letters). The error messages in the OP indicated a search was being made on 1. Quote Link to comment Share on other sites More sharing options...
Al Brookbanks Posted November 3, 2016 Share Posted November 3, 2016 Thanks guys. I'll get onto this ASAP. Quote Link to comment Share on other sites More sharing options...
nomem Posted November 3, 2016 Author Share Posted November 3, 2016 If it helps I actually got 4 errors not just the one I showed in the post. Yesterday, 05:06              File: [catalogue.class.php] Line: [1726] "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 fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%') LIMIT 9 OFFSET 0 " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%')' at line 1  Yesterday, 05:06              File: [catalogue.class.php] Line: [1728] "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 fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%') " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%')' at line 1  01 Nov 2016, 05:02         File: [catalogue.class.php] Line: [1726] "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 fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%') LIMIT 9 OFFSET 0 " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%')' at line 1  01 Nov 2016, 05:02         File: [catalogue.class.php] Line: [1728] "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 fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%') " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext '%1%' OR I.description fulltext '%1%' OR I.product_code fulltext '%1%')' at line              Quote Link to comment Share on other sites More sharing options...
bsmither Posted November 3, 2016 Share Posted November 3, 2016 Code at lines 1726 and 1728 (for your edited version of this file) are similar. The only difference is that the first actually requests nine records (one page worth) to display, and second is used to get the total number of records that match. Thus, the code now knows how many pages of results there are to build the pagination links: Page 1 of 7. Quote Link to comment Share on other sites More sharing options...
toast691 Posted December 14, 2016 Share Posted December 14, 2016 I Have also got the same error in my store error log. With a bit over 300 errors since upgrading to the latest version in November. File: [catalogue.class.php] Line: [1715] "SELECT I.* FROM ccmo_CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM ccmo_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 `ccmo_CubeCart_category_index` as CI INNER JOIN ccmo_CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (I.name fulltext '%dry%' OR I.description fulltext '%dry%' OR I.product_code fulltext '%dry%') LIMIT 30 OFFSET 0 " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext '%dry%' OR I.description fulltext '%dry%' OR I.product_code fulltext '%' at line 1 Quote Link to comment Share on other sites More sharing options...
bsmither Posted December 14, 2016 Share Posted December 14, 2016 This is Issue #1330. There is a fix to that fix, so read the whole page. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.