Post by lukeketchum1 » Sat Jul 13, 2019 4:40 pm

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

Active Member

Posts

Joined
Tue Jan 23, 2018 12:30 am

Post by letxobnav » Sat Jul 13, 2019 7:24 pm

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.

Crystal Light Centrum Taiwan


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm

Post by straightlight » Sat Jul 13, 2019 7:26 pm

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.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by letxobnav » Sat Jul 13, 2019 7:33 pm

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.

Crystal Light Centrum Taiwan


Active Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm

Post by lukeketchum1 » 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.

Active Member

Posts

Joined
Tue Jan 23, 2018 12:30 am

Post by straightlight » Sat Jul 13, 2019 10:04 pm

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.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by lukeketchum1 » Sun Jul 14, 2019 3:33 pm

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.

Active Member

Posts

Joined
Tue Jan 23, 2018 12:30 am
Who is online

Users browsing this forum: KarimIO and 8 guests