Post by tfsbs » Sat May 21, 2022 6:42 pm

I have recently migrated my OpenCart site to a new server. On the previous server, the shop URL was domain.com/official; but now, I've changed it to shop.example.com. I've updated the config.php files with the new URL as well as document root, and imported the database as well.

But now when I visit the URL, it shows the following error:-

Code: Select all

Warning: mysqli::query(): (21000/1242): Subquery returns more than 1 row in /www/wwwroot/shop.example.com/system/library/db/mysqli.php on line 18
Fatal error: Uncaught Exception: 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 oc_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 oc_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 ps.date_end FROM oc_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 in /www/wwwroot/shop.example.com/system/library/db/mysqli.php on line 40
I've already removed the duplicate entry inside the table oc_product_discount, but still getting the error. Can someone help me figure out a way to get the website live?

OpenCart version: 3.0.3.2

Edit 1: I've also removed the duplicate data on the table oc_product_special. The way I did that was by exporting the table, editing it with text editor to remove the duplicate lines, and then importing the SQL file. Let me know if this method can cause an issue as well.

Newbie

Posts

Joined
Sat May 21, 2022 4:51 pm

Post by joeantropy » Mon May 23, 2022 7:34 pm

It looks like the error is happening in the getProduct function of catalog/model/catalog/product.php. The query as reported in the error log is cut off (this is due to a character limit in either OpenCart's or PHP's method of error reporting).

The entire query also makes similar sub-queries on the following tables, but without the `LIMIT 1` clause that guards against the 'more than one row' error - check for duplicate data in these tables as well:
  • oc_product_reward
  • oc_stock_status
  • oc_weight_class_description
  • oc_length_class_description
This only covers the OpenCart core of course, and if there any extensions installed on the site, they may add their own bits to the query which could themselves be causing the error. It may be worth temporarily tweaking the code by means of a vQmod or OCmod (or if unable to, contact a developer such as ourselves to do so) to write the entire query out to a log file before executing it, to be able to inspect it more closely for all the sub-queries it contains. (On that note, if anyone can do the same using event hooks only, I'd love to see it!)

Newbie

Posts

Joined
Mon Mar 02, 2020 10:19 pm
Who is online

Users browsing this forum: No registered users and 261 guests