Slowness when changing product category and MySQL error

[cprats]

When I edit products, it takes a lot to save the modifications if I change the category. Sometimes I even get this error:

``` Link to database cannot be established: SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")

``` This only happens when editing older products, but newer ones update fast.

I'm using TB 1.0.7 migrated from PS 1.6.1.21

Any idea of what can cause this?

[dosbiner]

Enable debug profiling, and then try again. Now you can check what makes it slow in the bottom page.

[cprats]

I've enabled debug profiling and I've got all these messages:

Warning on line 265 in file /home/xxx/public_html/classes/PageCache.php [2] Invalid argument supplied for foreach()

Unknown error on line 280 in file /home/xxx/publichtml/modules/googleshopping/lib/phpseclib/Crypt/RSA.php [8192] Methods with the same name as their class will not be constructors in a future version of PHP; CryptRSA has a deprecated constructor

Unknown error on line 82 in file /home/xxx/publichtml/modules/googleshopping/lib/phpseclib/Crypt/Hash.php [8192] Methods with the same name as their class will not be constructors in a future version of PHP; CryptHash has a deprecated constructor

Unknown error on line 176 in file /home/xxx/publichtml/modules/googleshopping/lib/phpseclib/Math/BigInteger.php [8192] Methods with the same name as their class will not be constructors in a future version of PHP; MathBigInteger has a deprecated constructor

Unknown error on line 115 in file /home/xxx/publichtml/modules/shopgate/vendors/shopgatelibrary/vendors/JSON.php [8192] Methods with the same name as their class will not be constructors in a future version of PHP; sgServicesJSON has a deprecated constructor

Unknown error on line 796 in file /home/xxx/publichtml/modules/shopgate/vendors/shopgatelibrary/vendors/JSON.php [8192] Methods with the same name as their class will not be constructors in a future version of PHP; sgServicesJSON_Error has a deprecated constructor

I've deleted Google Shopping and Shopgate modules because they were not installed and I was not using them.

After deleting those modules, I only get the first warnig:

Warning on line 265 in file /home/xxx/public_html/classes/PageCache.php [2] Invalid argument supplied for foreach()

[datakick]

This warning has nothing to do with the slowness. You have probably enabled debug mode, not profiling. Enable profiling, and then look at the bottom of the page for performance statistics

That should give you the basic idea what's slow in your system

[cprats]

@datakick

Sorry, it was debug, you're right. This is the profiling result:

[datakick]

@cprats I don't see anything. Is this a result of a product save operation?

[cprats]

@datakick said in Slowness when changing product category and MySQL error:

@cprats I don't see anything. Is this a result of a product save operation?

Yes. The slowness only occurs with the products that were already in the store before the migration. After the change of category of those products, another change is fast as it should be.

[dosbiner]

Use debug profiling when you edit/save old products, there must be show the problem

[cprats]

@datakick I tried again and now I've got one of the slow products. It only happens if I change all categories. For example, a product in Categories-New-Covers will not experience a slow modification if changed to Categories-New. But, If I unselect all categories and I select a different category (for example, Sold Idems) saving process will be extremely slow. Here a screenshot of profiling while editing one of these products:

[datakick]

@cprats we are getting closer. There are over 9000 sql queries -- that's way too many. Normally you shouldn't see more than 300. I bet there's some very inefficient loop that's executing some query over and over.

Please look at the bottom part of the profile stats, and search for section with sql statements named Doubles. It looks something like this (the red number says how many time was this sql executed)

[cprats]

@datakick Here is the Doubles section in 6 screenshots:

[cprats]

It seems the problem is with idproduct. I am using this route to products in schemaurls: {categories:/}{rewrite} instead of the configuration I had when the core was Prestashop: {category:/}{id}-{rewrite}{-:ean13}.html

[datakick]

There is indeed very inefficient piece of code in classes/Product.php, function cleanPositions. When you remove product from a category, all products that are associated with that category and have higher position gets updated. In your case this leads to thousands of sql updates. They are very fast, but it adds up.

That's also the reason why you experience this problem only with olded products -- they have lower positions inside categories. New products have higher position, so the total number of updates is much lower.

I'll prepare a fix and push it to thirtybees 1.0.8. Meanwhile, this is what you can do to avoid this problem:

edit file classes/Product.php, find function cleanPositions, and remove these lines.

[cprats]

@datakick Thank-you so much for your help

[cprats]

I've removed the code you pointed out and now it works fine. Thanks again.