VirtueMart Forum

VirtueMart 2 + 3 + 4 => Virtuemart Development and bug reports => Topic started by: role74 on October 30, 2018, 19:32:01 PM

Title: Database overload in products inventory
Post by: role74 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
Title: Re: Database overload in products inventory
Post by: Studio 42 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
Title: Re: Database overload in products inventory
Post by: caesarsk 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.
Title: Re: Database overload in products inventory
Post by: role74 on October 30, 2018, 22:48:35 PM
...it's all standard VM, no modifications...
Title: Re: Database overload in products inventory
Post by: GJC Web Design 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 = ?
Title: Re: Database overload in products inventory
Post by: role74 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
Title: Re: Database overload in products inventory
Post by: GJC Web Design 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