[5.x]: (Database overload) Issues loading element index (products) when variant stock is added to the view table

[klick]

What happened?

Description

Loading element indexes errors out after loading for a few minutes when Stock is added to the view table. No report in storage/logs but the debug bar reveals a stack trace to the error.

Full Stack trace:

yii\web\BadRequestHttpException: Request must accept JSON in response in /home/httpd/vhosts/***/vendor/craftcms/cms/src/web/Controller.php:559
Stack trace:
#0 /home/httpd/vhosts/***/vendor/craftcms/cms/src/controllers/ElementIndexesController.php(108): craft\web\Controller->requireAcceptsJson()
#1 /home/httpd/vhosts/***/vendor/yiisoft/yii2/base/Controller.php(176): craft\controllers\ElementIndexesController->beforeAction(Object(yii\base\InlineAction))
#2 /home/httpd/vhosts/***/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('get-elements', Array)
#3 /home/httpd/vhosts/***/vendor/craftcms/cms/src/web/Application.php(350): yii\base\Module->runAction('element-indexes...', Array)
#4 /home/httpd/vhosts/***/vendor/craftcms/cms/src/web/Application.php(649): craft\web\Application->runAction('element-indexes...', Array)
#5 /home/httpd/vhosts/***/vendor/craftcms/cms/src/web/Application.php(312): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#6 /home/httpd/vhosts/***/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#7 /home/httpd/vhosts/***/web/index.php(12): yii\base\Application->run()
#8 {main}

Steps to reproduce

1. Clicking on Commerce > Products (View table has stock added)
2. Ajax takes forever (loader spinning), bringing the whole system to a halt
3. Errors out, nothing in the logs.

Expected behavior

List element indexes (products).

Actual behavior

Craft CMS version

5.5.5

Craft Commerce version

5.2.7

PHP version

8.2.25

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

• Asset Rev | 8.0.0
• Campaign | 3.5.7
• CKEditor | 4.4.0
• CP Clear Cache | 2.0.1
• Element API | 4.1.0
• Feed Me | 6.6.1
• Image toolbox | 3.0.1
• Minify | 5.0.0
• SEOmatic | 5.1.7
• Similar | 5.0.2
• Stripe for Craft Commerce | 5.0.4.3

[linear]

PT-2334 [5.x]: Issues loading element index (products) when variant stock is added to the view table

[lukeholder]

I cant reproduce this.

Could you try upgrading to 5.2.8 and see if you can reproduce it? Does it resolve itself after resaving the variants?

[klick]

Updated to 5.2.8. Problem still there.
Interestingly thats only an issue on my production environment. Both testing (same server) and local (ddev) are fine. Switching productioon to dev mode makes no change.

Resaving variants: is there a console command for this?

[klick]

So, did run resave/products which did hang. Checked the products and there was one with capitals in its variant SKU. I tried to edit that product but the CP threw a 500. I had a candidate. Lowercased its variants SKU in the db but no change. Ended up deleting that product and now the element index executes as expected. Don’t know for sure where that product was malformed.

[klick]

So my hoster said there is a database overload problem and locked the domain. It seems that Commerce creates that overload to show element indexes. Here is the query which they identified as a problem (they sent a screenshot):

I’m puzzled by that since there are only some 50 products, one variant per product and two people operating the site. But anyhow:
How can I optimize those db queries?

[nfourtythree]

Hi @klick

Thank you for the information. What database engine are you running? Are you seeing the same issue when running the project locally?

Thanks!

[klick]

Hi @nfourtythree thanks for replying.

On the remote → Server version: 10.6.18-MariaDB-log - MariaDB Server
No problems locally (ddev) or on another host.

Also, the host requires the server to be referenced with the port attached: localhost:3306 in the .env. It works without but then changes in the project.yaml won’t get applied. If attached however, a database backup via the CP is not possible.

Hoster is metanet.ch

[nfourtythree]

Hi @klick

Thank you for the extra info. Are you running MySQL or MariaDB in ddev locally?

It sounds like this might be the issue https://jira.mariadb.org/browse/MDEV-28852 the fix for which looks to have been included in MariaDB 10.10.1.

Not sure if this is something that can be updated on your host? Or on a staging server to test to see if it makes a difference.

[klick]

Thank you @nfourtythree

I will pass that on to the hosting company. Hopefully that will be it. Will get back here once I know more.
Locally it’s mysql.

[klick]

Some additional info: Using the CP was generally slow but routinely errored out using a non-admin Account when viewing element indexes (products) or editing products itself. Here the variant window spinned forever before stopping with a server-error flyout. Those actions were unproblematic when using an admin-Account.

[klick]

So, the hosting company came back with the info that with the shared hosting plan of the client, the MariaDB-version can not be changed. They suggested a managed server instead. About ten times the price. I’m a bit shocked. Anything I could do on my end?

[lukeholder]

@klick can you switch to MySQL?

[klick]

@lukeholder Unfortunately not.