News:

Support the VirtueMart project and become a member

Main Menu

Database overload in products inventory

Started by role74, October 30, 2018, 19:32:01 PM

Previous topic - Next topic

role74

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

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

#2
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

...it's all standard VM, no modifications...

GJC Web Design

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 https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 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
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

role74

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

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 https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 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
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation