Error 500 on certain search terms

Hi all,

I have a live opencart 3.0.2.0 setup and have recently noticed a problem when I search using the main search box with certain terms

Specifically in this instance I am searching for "breakpoint code" at titancards.co.uk and its throwing an error 500. But I've tried searching for loads of other stuff and its fine. Likewise ive tried searching for "breakpoint" and "code" and both of those work fine too - its most unusual

I have cleared the main opencart dashboard cache, my themes cache and my browsers cache and its still the same. I had a look in the error log folder and found this stuff (in the main root/errors.log)

[13-Jul-2019 06:42:34 UTC] PHP Fatal error: Uncaught exception 'Exception' with message 'Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM tc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM tc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM tc_product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '1') AS reward, (SELECT ss.name FROM tc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_st in /home/xx/titancards/system/library/db/mysqli.php on line 40
[13-Jul-2019 06:42:41 UTC] PHP Fatal error: Uncaught exception 'Exception' with message 'Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM tc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM tc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM tc_product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '1') AS reward, (SELECT ss.name FROM tc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_st in /home/xx/titancards/system/library/db/mysqli.php on line 40
[13-Jul-2019 06:42:46 UTC] PHP Fatal error: Uncaught exception 'Exception' with message 'Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM tc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM tc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM tc_product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '1') AS reward, (SELECT ss.name FROM tc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_st in /home/xx/titancards/system/library/db/mysqli.php on line 40

I don't suppose anyone knows what the issue may be?

Best regards

Luke

well, it states:

Error: Subquery returns more than 1 row

so print the actual product query in your error_log and run each sub-query in there with phpmyadmin or something like that on your database and see which sub-query returns more than 1 row when it shouldn't.

In this scenario, there's nothing wrong with the sub-queries. It is rather caused by an installed extension using filters or due to a basic and limited host packaging.

not saying there is a problem with the sub-queries, that is the default product query, I am saying there might be a problem with his data.

Thanks for replies

Which bit is the subquery, and I guess I would enter it in SQL in PhpMyAdmin with my OC database selected? I don't know anything about SQL really.

lukeketchum1 wrote: ↑

Sat Jul 13, 2019 8:53 pm

Thanks for replies

Which bit is the subquery, and I guess I would enter it in SQL in PhpMyAdmin with my OC database selected? I don't know anything about SQL really.

If you do not have the expertise, you could always contact me or create a service request in the Commercial Support section of the forum to get this done as a custom job.

Thanks all for assistance.
Turned out it was a duplicate value in reward points of all things for a product ID which appears when 'breakpoint code' is searched. After removing the duplicate reward point info the search returned to normal.