Post by Jansuh » Thu Aug 09, 2018 7:05 pm

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!
Last edited by Jansuh on Thu Aug 09, 2018 8:19 pm, edited 1 time in total.

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


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

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

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

Post by Jansuh » Thu Aug 09, 2018 8:20 pm

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

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by thekrotek » Thu Aug 09, 2018 8:24 pm

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

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Jansuh » Thu Aug 09, 2018 8:25 pm

Ok, can you tel me how to do that?

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by MrPhil » Thu Aug 09, 2018 8:56 pm

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.

User avatar
Active Member

Posts

Joined
Wed May 10, 2017 11:52 pm

Post by Jansuh » Thu Aug 09, 2018 9:44 pm

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

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by Jansuh » Thu Aug 09, 2018 11:17 pm

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'

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by thekrotek » Thu Aug 09, 2018 11:34 pm

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

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Jansuh » Fri Aug 10, 2018 1:27 am

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

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by thekrotek » Fri Aug 10, 2018 1:32 am

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

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Jansuh » Tue Aug 14, 2018 3:15 pm

Allright, Anybody able to commercially help me solve this issue? Please contact me
Last edited by Jansuh on Tue Aug 14, 2018 4:19 pm, edited 2 times in total.

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by thekrotek » Tue Aug 14, 2018 3:52 pm

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.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Jansuh » Tue Aug 14, 2018 4:18 pm

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 ?

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by Jansuh » Tue Aug 14, 2018 5:24 pm

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?

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by thekrotek » Tue Aug 14, 2018 5:33 pm

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

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Jansuh » Tue Aug 14, 2018 7:15 pm

Question still remains. How do i solve this?

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by Jansuh » Wed Aug 15, 2018 4:32 pm

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

New member

Posts

Joined
Fri Apr 20, 2018 8:13 pm


Post by bgosaric » Mon Dec 10, 2018 11:36 pm

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.

Newbie

Posts

Joined
Mon Dec 10, 2018 11:29 pm
Who is online

Users browsing this forum: No registered users and 159 guests