Post by SXGuy » Mon Jul 31, 2017 2:09 am

I appear to be getting a max questions database error when adding around 20 products to cart.

Fatal error: Uncaught exception 'Exception' with message 'Error: User '173672_menu' has exceeded the 'max_questions' resource (current value: 50000)<br />Error No: 1226<br />SELECT * FROM oc_extension WHERE `type` = 'openbay'' in /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/system/library/db/mysqli.php:40 Stack trace: #0 /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/system/library/db.php(16): DB\MySQLi->query('SELECT * FROM o...', Array) #1 /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/system/library/openbay.php(117): DB->query('SELECT * FROM o...') #2 /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/system/library/openbay.php(10): Openbay->getInstalled() #3 /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/vqmod/vqcache/vq2-system_storage_modification_system_engine_loader.php(110): Openbay->__construct(Object(Registry)) #4 /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/system/framework.php(81): Loader->library('openbay') #5 /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/vqmod/vqcache/vq2 in /srv/disk7/173672/www/menu.moonlite-solutions.co.uk/system/library/db/mysqli.php on line 40

I have tested a few times, and it doesnt really matter which extension it fails on, or anything database call for that matter, its all random, the only thing that isnt is adding products to the cart before this error occurs.

I understand my shared hosting wont increase the limit however, surely adding 20 odd products should not cause this error?

If it is any help, im also running multistores.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by artcore » Mon Jul 31, 2017 4:26 am

Maybe you can ask your host the log file for this user so you can see which calls are made in abundance. Or add a logger to the mysqli.php if they won't do it. An extension might exist for such even.
Otherwise no one could know the cause. Maybe a ddos even (a bot perhaps or more malicous). 50k questions per hour?

User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by SXGuy » Mon Jul 31, 2017 4:58 am

Thanks for your reply, its unlikey to be a bot, since the error occours speficially when adding a lot of products to the cart, This isnt somthing which happens randomly, or at a timed event like a bot would cause.

It could be an extension which is for some reason causing a database query loop, however, i see no error in log file.

Because this only happens while adding products to the cart i am at a loss as to what would cause it, from what i understand, there are no database queries during this process as nothing is added to the database until you proceed to checkout? isnt adding items to the cart prior to checkout just handled by JSON event?

I would ask my host to confirm what is exactly querying the database to many times but i cant see id get a response since i dont believe anything would be.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by artcore » Mon Jul 31, 2017 2:17 pm

I got curious and added a logger. Counted about 100 queries for adding a single product ;D
See screenshot for adding to mysqli.php in system>library>db

Code: Select all

//$log = new \Log('db'.date('Y-m-d').'.log');
		//$log->write($sql);
Remove/add the // to enable/disable. It will create a log file for each day so you won't have a too giant single file.

JSON event or AjAX does contact the server but there's no page refresh needed.

Attachments

Screenshot_2017-07-31-08-10-55.jpg

Screenshot_2017-07-31-08-10-55.jpg (340.54 KiB) Viewed 416 times


User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by SXGuy » Mon Jul 31, 2017 3:05 pm

Wow 100 per product? OK but even if I add 50 products that's still only 5000 queries. I'm 45000 queries short of the error lol ok so the page has a logo header 5 modules a banner an article etc etc. Can't see that adding up to 50000. But I'll use your logger code and check it out. Thank you!

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by SXGuy » Mon Jul 31, 2017 5:26 pm

Ok so i ran the test, something clearly isnt right.
After adding 34 products to the cart and clicking checkout the site crashed, i checked the error log for information.

Bare in mind this is everything that got queried just on that page alone, i typed the exact url location of the page in to the browser before running the log file script, so it doesnt include any other queries for the rest of the site.

Adding 34 products, and clicking checkout resulted in over 50000 SELECT queries alone.

I get that one customer may not add 34 products in one session, but what if 10 people add 3? will this not still cause max questions to go over 50000 within an hour?

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by artcore » Mon Jul 31, 2017 5:46 pm

Anything in the log that stands out? You got 50k entries? You can PM me the file if not too large:)
And don't forget to disable the logging!!!

User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by SXGuy » Mon Jul 31, 2017 6:16 pm

Thank you, yes ive turned error logs off.

I will PM you the log file, 50512 SELECT queries to be exact when adding 34 products to the cart.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by artcore » Mon Jul 31, 2017 7:52 pm

It seems a ton of your db queries are called from a loop, maybe from your home page or category which has a huge! list of products. You should ask the developer of this to make it more optimized, iow don't query the db on each iteration.
However, oc kinda does the same thing. Guess it would only be an issue with limited web hosting but still has room for improvement.

You could also start using a page cache like the free budgetneon's one which is in the extension market. This prevents calling the db for static content.
Hope it helps,
Cheers

User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by SXGuy » Mon Jul 31, 2017 8:07 pm

Thank you. Ironically I was looking in to page caching. That maybe the answer.

Unfortunately the site requires a menu style category product layout and so it uses a category product module to display all products on one page hence why you see multiple dB queries. There may be a better way to optimise it but I would need to contact the developer.

Either way I think perhaps a page cache may be the way forward.

Thanks for the help.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by SXGuy » Fri Aug 04, 2017 3:19 pm

Just an update. I indexed some of the tables that get used alot. I have added a cache extension and I've also ran memcache. Problem still persists. I've tried direct from a category page and problem still happens. What I've noticed is opencart calls the cart table for all products in a category everytime you add 1 product to the cart. How can that be right? 14 products in one category. Cart is called 14 times just by adding 1 product to cart. With code like that I'm not suprised the site crashes. This is the first oc install I've done with so many modules and products prior to oc changing from session based cart to table based cart. Prior to this I used oc below 2.1.

I can not help but feel this change has dramatically increased database queries and have limited the amount of people that can use it on shared hosting.

The fact I need multistore adds to the issue.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by artcore » Fri Aug 04, 2017 6:06 pm

I just checked too. Opened home, added 2 products to cart from featured module. One had recurring profile so it went to product page, etc, added to cart. Total queries 789.
It probably loops through everything to check for stock and others like specials, discounts and so on for every add to cart action. I can imagine doing this for 14 products would quickly exhaust the 50k limit you have. Unfortunately you can't cache the dynamic content like carts.

Either find a better host or see how the competition handles this. I'm suspecting not much of a difference for other webshop solutions. I agree it seems a bit too much...

User avatar
Active Member

Posts

Joined
Tue Jul 09, 2013 4:13 am
Location - The Netherlands

Post by SXGuy » Fri Aug 04, 2017 9:04 pm

Thanks for your input. Luckily for me it's my friend's problem as I designed the site for them. But it looks like they will be spending some money on a decent vps or semi dedicated server. It would have been only a matter of time before they needed to do it anyway I guess. My host is just set up as a temp host while the design takes place so luckily for me I'll be rid of this nightmare soon enough.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by MrPhil » Fri Aug 04, 2017 10:54 pm

It certainly does sound like OC is doing an excessive number of DB queries during storefront activities (putting items in cart, checking out). A couple of things to think about:

  1. Can a query for each product be consolidated into one query for all the products? At some point it may be cheaper to get a big chunk of data and trim it down in PHP to get to the relevant data. I hope OC isn't making multiple queries for each item in the cart.
  2. Has anyone looked into some sort of DB caching? That is, hold on to the queried data in-memory, rather than constantly going back to the DB to refresh it. Obviously, for something like product stock level, a query would have to be done during checkout to ensure that stock isn't overdrawn, but it would be only a small inconvenience compared to constantly checking. How likely is the product price to change while someone is shopping? Maybe there could be a flag indicating table updates and the need to refresh the cache.
It's a matter of trading off algorithm elegance and up-to-the-second status changes for efficiency gained by fewer DB actions (but a larger VM footprint). Somewhere there must be a sweet spot.

Active Member

Posts

Joined
Wed May 10, 2017 11:52 pm

Post by SXGuy » Fri Aug 04, 2017 11:47 pm

Well my friend has a product list much like a takeaway menu. So all he needs is categories and products. Maybe special price as well. But none of the other stuff like reoccurring profiles, model, sku etc etc. I had thought about pulling all product data and ordering by category I'd then running that inside a for each categories as category statement in the controller. Hoping to somehow order products and categories since the page displays all results anyway. However the issue for me is the cart. Why it needs to check the cart table for customer id the same amount of times for each product in the category for only 1 added product to the cart is beyond me. Surely all it need do is run one check to compare if the product is stored in the cart or not and whether a customer id is assigned to it. If not run the normal process of changing customer Id and adding to order table once checkout is complete. Seems a logical way of doing it to me.

Ideally I'd like to store the cart contents in a cache and just simply send it to the order table on completion bypassing all the cart table queries.

I built a similar site years ago using 1.5.6 and I must say I never had this trouble. So I can only assume it relates to the change that was made with handling cart contents from session based to database.

I've even gone to the lengths of converting ocmods back to vqmods to reduce the number of queries.

I also notice oc runs the same queries over and over. Extensions and openbay for example. All not needed. They should be checked once and stored somewhere until a change is made. It could all be triggered simply by clearing the cache. I must say I'm disappointed with the way oc has changed and can not understand the reason behind it. Maybe I've missed something.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by SXGuy » Fri Aug 04, 2017 11:53 pm

What if I manually add all products to the cart table with customer id as 0 first? Reducing the initial insert queries to 0. Then instead of just changing id and moving to order table it could just copy and change customer id before inserting to order table? Would that reduce the mumber of queries?

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by IP_CAM » Sat Aug 05, 2017 12:56 am

I got curious too, and added 50 Products, by help of the Auto
ProductsGenerator Extension, and those 50 products generated
a 2182 line Log by use of my Version 1.5.6.5, just to mention this!
Ernie

Ernie's OpenShop v.1.75 + OpenCart v.1.5.6.5 LIGHT Test Sites with free responsive Themes
http://www.bigmax.ch/shop/ - http://www.ebikes.li - http://www.ipc.li/shop/ - http://www.openshop.li/shop/
Image


User avatar
Guru Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by SXGuy » Sat Aug 05, 2017 1:08 am

There you go then. In oc2.3.0.2 when adding around 30 products it generated over 50k select queries.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by SXGuy » Sat Aug 05, 2017 1:13 am

IP_CAM wrote:
Sat Aug 05, 2017 12:56 am
I got curious too, and added 50 Products, by help of the Auto
ProductsGenerator Extension, and those 50 products generated
a 2182 line Log by use of my Version 1.5.6.5, just to mention this!
Ernie
I'm curious. Where can I get a copy of your version? I'm seriously considering reverting back.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by IP_CAM » Sat Aug 05, 2017 1:51 am

Well, I could spare of of my Test Shops, already relatively heavy mocked up,
but to make sure, it would be advisable, to have it installed by me, since I
am not yet ready to offer it as ready-to-go installation.
Check the 3 Links on my Comment-Footer, to find the 3 differently themed
Versions. :D
Ernie

PS: But only, if you promise, not to SELL it afterwards, it's not planned to be
sold, in the first Place, despite of the fact, that it works better than anything,
i have tested, before and after it, so far...

Ernie's OpenShop v.1.75 + OpenCart v.1.5.6.5 LIGHT Test Sites with free responsive Themes
http://www.bigmax.ch/shop/ - http://www.ebikes.li - http://www.ipc.li/shop/ - http://www.openshop.li/shop/
Image


User avatar
Guru Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: No registered users and 6 guests