OpenCart Community

Hi all,

I am running opencart 2.3.0.2 with the default theme on a hosted linux server and am having the following problem which wasn't there before:

When i try to edit a product or when i click on the button to make a packing slip i get a blank screen. Chrome browser gives http error 500

The error log gives me the following error:
2018-08-09 12:55:41 - PHP Warning: mysqli::query(): (21000/1242): Subquery returns more than 1 row in /home/pureandtim/domains/pureandtimeless.nl/public_html/system/library/db/mysqli.php on line 18

I've searched the forum and found something about maybe double entries in the database but i don't know how to step by step troubleshoot this and find the problem entry in the database.
viewtopic.php?t=161273

The problem occurs on all products and on every order of which i try to print a packing slip.

Any of you able to point me either in the right direction or have a solution. Highly appreciated!

I've searched the forum and found something about maybe double entries in the database

Source unprovided. Forum rules.

straightlight wrote: ↑

Thu Aug 09, 2018 8:06 pm

I've searched the forum and found something about maybe double entries in the database

Source unprovided. Forum rules.

Original post edited, thanks for the heads up

There's no generic solution for this, only possible to say something by looking at the query itself.

Ok, can you tel me how to do that?

The first step is to find the complete error message logged somewhere. You might have to temporarily change an error setting somewhere to get the full message, looking like the one shown in the topic you referred to. Once you have that, start picking apart the subqueries (SELECT ... ) AS fieldname,. Go into phpMyAdmin and manually run that subquery (copy-paste it into the SQL query, just the part between () ), and see if it provides more than one row of data. If it does, that's probably the problem area with some duplicate data entries to deal with. What to do at that point will likely depend on what sort of abnormalities you find.

Ok, i will try and get more extensive eroor logging going. Thanks for now

Hi again,

I was able to track the querys that were send to the database and i see that there are double querys sent when i try and get the packing slip opened.
Any suggestions? Am i on the right track?

Here is what i found:
SELECT * FROM oc_setting WHERE store_id = '0'
SELECT * FROM `oc_language` WHERE code = 'nl-nl'
SELECT * FROM oc_customer WHERE customer_id = '34' AND status = '1'
UPDATE oc_customer SET language_id = '2', ip = '83.82.250.x' WHERE customer_id = '34'
SELECT * FROM oc_customer_ip WHERE customer_id = '34' AND ip = '83.82.250.x'
SELECT * FROM oc_currency
SELECT tr1.tax_class_id, tr2.tax_rate_id, tr2.name, tr2.rate, tr2.type, tr1.priority FROM oc_tax_rule tr1 LEFT JOIN oc_tax_rate tr2 ON (tr1.tax_rate_id = tr2.tax_rate_id) INNER JOIN oc_tax_rate_to_customer_group tr2cg ON (tr2.tax_rate_id = tr2cg.tax_rate_id) LEFT JOIN oc_zone_to_geo_zone z2gz ON (tr2.geo_zone_id = z2gz.geo_zone_id) LEFT JOIN oc_geo_zone gz ON (tr2.geo_zone_id = gz.geo_zone_id) WHERE tr1.based = 'shipping' AND tr2cg.customer_group_id = '1' AND z2gz.country_id = '150' AND (z2gz.zone_id = '0' OR z2gz.zone_id = '2336') ORDER BY tr1.priority ASC
SELECT tr1.tax_class_id, tr2.tax_rate_id, tr2.name, tr2.rate, tr2.type, tr1.priority FROM oc_tax_rule tr1 LEFT JOIN oc_tax_rate tr2 ON (tr1.tax_rate_id = tr2.tax_rate_id) INNER JOIN oc_tax_rate_to_customer_group tr2cg ON (tr2.tax_rate_id = tr2cg.tax_rate_id) LEFT JOIN oc_zone_to_geo_zone z2gz ON (tr2.geo_zone_id = z2gz.geo_zone_id) LEFT JOIN oc_geo_zone gz ON (tr2.geo_zone_id = gz.geo_zone_id) WHERE tr1.based = 'store' AND tr2cg.customer_group_id = '1' AND z2gz.country_id = '150' AND (z2gz.zone_id = '0' OR z2gz.zone_id = '2336') ORDER BY tr1.priority ASC
SELECT * FROM oc_weight_class wc LEFT JOIN oc_weight_class_description wcd ON (wc.weight_class_id = wcd.weight_class_id) WHERE wcd.language_id = '2'
SELECT * FROM oc_length_class mc LEFT JOIN oc_length_class_description mcd ON (mc.length_class_id = mcd.length_class_id) WHERE mcd.language_id = '2'
DELETE FROM oc_cart WHERE (api_id > '0' OR customer_id = '0') AND date_added < DATE_SUB(NOW(), INTERVAL 1 HOUR)
UPDATE oc_cart SET session_id = 'cleuiepom2p93rscvoa8l44s55' WHERE api_id = '0' AND customer_id = '34'
SELECT * FROM oc_cart WHERE api_id = '0' AND customer_id = '0' AND session_id = 'cleuiepom2p93rscvoa8l44s55'
SELECT * FROM oc_extension WHERE `type` = 'openbay'
SELECT * FROM `oc_event` ORDER BY `code` ASC
SELECT * FROM oc_user WHERE user_id = '1' AND status = '1'
UPDATE oc_user SET ip = '83.82.250.x' WHERE user_id = '1'
SELECT permission FROM oc_user_group WHERE user_group_id = '1'

You need to find the exact query, mentioned in error, not post all queries you can track

If i only knew how to do that .........

In this case the only way for you is to ask for commercial support.

Allright, Anybody able to commercially help me solve this issue? Please contact me

Jansuh wrote: ↑

Tue Aug 14, 2018 3:15 pm

Allright, Anybody able to commercially help me solve this issue? Please contact me

Most developers have contacts in their signature. You can drop an email or PM to any chosen developer yourself.

Was able to find this error in my server log when editing product:

[Tue Aug 14 09:17:42.990556 2018] [fcgid:warn] [pid 88885:tid 140543510062848] [client 83.82.250.9:62881] mod_fcgid: stderr: PHP Fatal error: Uncaught Exception: Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, (SELECT keyword FROM oc_url_alias WHERE query = 'product_id=520') AS keyword FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '520' AND pd.language_id = '2' in /home/pureandtim/domains/pureandtimeless.nl/public_html/system/library/db/mysqli.php:40, referer: https://www.pureandtimeless.nl/myadmind ... l4T08Hikk8

And this when editing category:

[Tue Aug 14 09:55:17.481229 2018] [fcgid:warn] [pid 88869:tid 140543904519936] [client 83.82.250.9:64035] mod_fcgid: stderr: PHP Fatal error: Uncaught Exception: Error: Subquery returns more than 1 row<br />Error No: 1242<br />SELECT DISTINCT *, (SELECT GROUP_CONCAT(cd1.name ORDER BY level SEPARATOR '&nbsp;&nbsp;&gt;&nbsp;&nbsp;') FROM oc_category_path cp LEFT JOIN oc_category_description cd1 ON (cp.path_id = cd1.category_id AND cp.category_id != cp.path_id) WHERE cp.category_id = c.category_id AND cd1.language_id = '2' GROUP BY cp.category_id) AS path, (SELECT DISTINCT keyword FROM oc_url_alias WHERE query = 'category_id=62') AS keyword FROM oc_category c LEFT JOIN oc_category_description cd2 ON (c.category_id = cd2.category_id) WHERE c.category_id = '62' AND cd2.language_id = '2' in /home/pureandtim/domains/pureandtimeless.nl/public_html/system/library/db/mysqli.php:40, referer: https://www.pureandtimeless.nl/myadmind ... l4T08Hikk8

When i run these querys on phpmyadmin i get Subquery returns more than 1 row error
SELECT DISTINCT *, (SELECT keyword FROM oc_url_alias WHERE query = 'product_id=520') AS keyword FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE p.product_id = '520' AND pd.language_id = '2'

or
SELECT DISTINCT *, (SELECT GROUP_CONCAT(cd1.name ORDER BY level SEPARATOR '&nbsp;&nbsp;&gt;&nbsp;&nbsp;') FROM oc_category_path cp LEFT JOIN oc_category_description cd1 ON (cp.path_id = cd1.category_id AND cp.category_id != cp.path_id) WHERE cp.category_id = c.category_id AND cd1.language_id = '2' GROUP BY cp.category_id) AS path, (SELECT DISTINCT keyword FROM oc_url_alias WHERE query = 'category_id=62') AS keyword FROM oc_category c LEFT JOIN oc_category_description cd2 ON (c.category_id = cd2.category_id) WHERE c.category_id = '62' AND cd2.language_id = '2'

So i guess i am closer to determining the cause. Anybody able to help me with this ?

As an addition to the above i found this:

http://trueliarx.blogspot.com/2017/03/c ... n-url.html

When i run this query i get a whole list of products and categories apperently having something larger then 1. (they all have 2, guess it should be 1)
SELECT `query`, COUNT(*) FROM `oc_url_alias` GROUP BY `query` HAVING COUNT(*) > 1

Question that remains is what now? what do i exactly have to do to solve this?

A typical error, when you install SEO extension and then disable/uninstall it. Or something like that.

Question still remains. How do i solve this?

Solved!

restored the url_alias part with phpmyadmin from a backup and everything seems to work ok now.

Just lost my homepage layout wich is strange but i can restore that pretty quick

I know that the problem was "solved" as mentioned, but i would like to share the reason why it happens. the "Subquery returns more than 1 row" simple occur when you have at the _url_alias table more than one register referring to the same SECTION/ID. Since the problem was with products probably there was a duplicate entry for Product1 for example. Something like: url_alias_id=1, product_id=1, keyword=product-1 / url_alias_id=2, product_id=1, keyword=product-1. By removing duplicities will solve the problem.