[5.x]: 5.2.2 Variant entry field hangs up MySQL Server

[apitel]

What happened?

Description

After upgrading to Craft Commerce 5.2.2 (and subsequently 5.2.2.1), we are noticing a major slowdown in Variant Queries, bringing MySQL to its knees. We have a variant select field in an entry, that, upon clicking, sends MySQL CPU usage to 100% continuously.

Issue does not occur with any other area of Craft and did not occur before upgrading to 5.2.2 (other than ~ 3x variant query slowdown from Craft 4 previously reported). Issue does not occur when adding an item to an order in CP.

Steps to reproduce

1. Add a variant select field to an entry type
2. Create a new entry of that type and select variant using variant select field

Expected behavior

1. List variants on the order of seconds

Actual behavior

1. MySQL usage goes to 100%+ and stays at 100% unless MySQL server is restarted (waited 10+ minutes)
   

Craft CMS version

Craft Pro 5.4.10.1

Craft Commerce version

5.2.2.1

PHP version

8.3.13

Operating system and version

Linux 5.15.0-124-generic

Database type and version

MySQL 8.0.39

Image driver and version

GD 8.3.13

Installed plugins and versions

[linear]

PT-2307 [5.x]: 5.2.2 Variant query slowness, unusable

[apitel]

Issue has been verified on multiple development, staging and production environments and there is nothing in the logs.

[angrybrad]

@apitel a few questions:

• Can you verify this is actual MySQL and not MariaDB (some distros make it hard to distinguish). Running a SELECT VERSION(); query should be good enough.

• Can you verify that your database charsets and collations are consistent across all tables and textual columns? This console command can help if they’re not: https://craftcms.com/docs/5.x/reference/cli.html#db-convert-charset

• Can you run the https://dev.mysql.com/doc/refman/8.4/en/mysqlcheck.html command with the --optimize flag against the database? It will rebuild table statistics and indexes in the database.

Assuming you still have the issue after that, and it's reproducible in different environments, can you send a database dump, your composer.json/lock files, and some steps to reproduce what you see to [email protected]? We can dig in further from there.

[apitel]

Below is the output of SELECT VERSION(); on our production server:

Locally, our team is running DDEV.

I can confirm the charsets and collations are set consistently across the table as utf8mb4 and utf8mb4_unicode_ci. I reran php craft db/convert-charset utf8mb4 utf8mb4_unicode_ci for good measure.

I ran mysqlcheck --optimize on our production database. The output was Table does not support optimize, doing recreate + analyze instead for each table, but it did complete successfully.

The issue described still persists. I will email the requested items to support@. Just to reiterate, this issue was new after upgrading to Commerce 5.2.2.

[white-lukas]

We are also seeing a serious spike in database cpu after deploying a commerce 5.2.2 update

[nfourtythree]

Hi All

Thank you for all the information provided and your patience on this issue.

We believe we have identified the cause of this issue and have pushed up a fix which will be included in the next release of Commerce.

To get this early, change your craftcms/commerce requirement in your project's composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev as 5.2.2.1",
  "...": "..."
}

Then run composer update.

If you have a chance to try this out before the release and let us know if it solves the issue that would be fantastic.

Thanks

[apitel]

We reinstalled the vendor directory with this new dev version of Craft Commerce. All other plugins have been disabled.

We did not see any resolution. We are still experiencing the same issue as reported.

[apitel]

If we move with the same latest db for 5.2.2.1, but use old commerce version "5.1.3" then the field works.

We then stepped through the various versions to see when the problem was introduced using a 5.x-dev database. Our results are as follow:

5.1.3 ✅
5.1.4 ✅
5.2.0 ✅
5.2.1 ✅
5.2.2 🔴
5.2.2.1 🔴
5.x-dev 🔴

[nfourtythree]

Hi @apitel

We have pushed further updates for this issue into the just released Commerce 5.2.4

Could you please try updating to this version and let us know if it makes any difference to your project? We have had reports from others that this update has made a significant improvement.

Thanks!

[apitel]

We've deployed the latest version of Craft Commerce 5.2.4 to local development (DDEV), staging, and production environments. The issue remains the same, which is when selecting the Variant field in an entry, after 2 minutes of trying to load it results in a Server Error and times out, MySQL Server runs continuously at 100% CPU and the only resolution is to restart MySQL service; there is nothing I can see in the logs. This issue was repeated in all environments.

I think this issue is distinct from the variant performance issues and was introduced in 5.2.2.