Author Topic: Database overload in products inventory  (Read 275 times)

role74

  • Beginner
  • *
  • Posts: 18
    • Dekostop Onlineshop for technical and advanced Divers
  • Skype Name: role74
  • VirtueMart Version: 3.0.10
Database overload in products inventory
« on: October 30, 2018, 19:32:01 pm »
Hi there

I've got a notice from my webhoster that the server gets overloaded in certain moments when a database query is send from my site. As far that I figured out, it happens when I go to the products inventory and choose to show products with low inventory. This happens since upgrading to VM 3.4.2.
See also the attached query that causes the problem.

Any clues how to solve this? Don't want to get my site shut down by the hoster...

I'm using virtuemart 3.4.2 with Joomla 3.8.13.
Database MySQLi 5.5.5-10.1.35-MariaDB and PHP 7.2.11

any help is welcome.

cheers from switzerland
role

Studio 42

  • Contributing Developer
  • Sr. Member
  • *
  • Posts: 3466
  • Joomla & Virtuemart addon developper
    • Studio 42 - Virtuemart & Joomla extentions
  • VirtueMart Version: 2.6 & 3.0.x.y
Re: Database overload in products inventory
« Reply #1 on: October 30, 2018, 19:58:23 pm »
In your query.
Why all this products id ?
ORDER BY RAND() ????
I dont checked the code, but the query is really strange in the back-end

caesarsk

  • Jr. Member
  • **
  • Posts: 50
Re: Database overload in products inventory
« Reply #2 on: October 30, 2018, 20:49:16 pm »
Yep. product.php (administrator/models)...
With this problem I have encountered too, but since I did modify this file, I was not paying attention at this time.

This is what happens to me if I use module VM_products and set a product order according to the best seller and show 10 products (cetegory id 0, products in shop 20k+). if the display of fewer products (eg. 2) is set, the error will not occur.

role74

  • Beginner
  • *
  • Posts: 18
    • Dekostop Onlineshop for technical and advanced Divers
  • Skype Name: role74
  • VirtueMart Version: 3.0.10
Re: Database overload in products inventory
« Reply #3 on: October 30, 2018, 22:48:35 pm »
...it's all standard VM, no modifications...

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 8433
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.2.14
Re: Database overload in products inventory
« Reply #4 on: October 30, 2018, 23:00:15 pm »
you must have some other plugin etc ..

administrator/index.php?option=com_virtuemart&view=inventory

the query for that view - low inventory is

SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`
      
  FROM `4k9_virtuemart_products` as p   
 
  LEFT JOIN `4k9_virtuemart_product_categories` as pc
  ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
 
  WHERE (p.`product_in_stock`- p.`product_ordered` < p.`low_stock_notification`)
 
  group by p.`virtuemart_product_id`
 
  ORDER BY `pc`.`ordering` DESC, p.`virtuemart_product_id` DESC
  LIMIT 0, 30

and then some more for media and details etc

that query you post makes no sense in the admin .. why would any admin task require a RAND display? and why all the NOT = ?
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

role74

  • Beginner
  • *
  • Posts: 18
    • Dekostop Onlineshop for technical and advanced Divers
  • Skype Name: role74
  • VirtueMart Version: 3.0.10
Re: Database overload in products inventory
« Reply #5 on: November 08, 2018, 20:28:37 pm »
Hi all

I've got it...it was just bad luck it happened when checking the product inventory.

Some time ago I changed the home main menu entry from the old standard layout to the new virtuemart category layout menu and disabled the legacy mode in the shop configuration.
This was the point where all began.

Don't know if it's caused by the template or the category view layout but it stopped when I switched back.

Any clues where to look in order to get it solved?

cheers
-role
 

GJC Web Design

  • 3rd party VirtueMart Developer
  • Super Hero
  • *
  • Posts: 8433
  • Virtuemart, Joomla & php developer
    • GJC Web Design
  • VirtueMart Version: 2.6.22 & 3.2.14
Re: Database overload in products inventory
« Reply #6 on: November 08, 2018, 23:17:50 pm »
compare your template files with the standard ones -- from memory it is only category template stuff that has legacy etc
GJC Web Design
VirtueMart and Joomla Developers - php developers http://www.gjcwebdesign.com
VM3 AusPost Shipping Plugin - e-go Shipping Plugin - VM3 Postcode Shipping Plugin - Radius Shipping Plugin - VM3 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
http://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation