our magento instance has a lot of products. An update of one product using the soap v2 interface is running into a timeout. If I set the timeout to 40 minutes, I get the following error message:

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away, query was: SELECT core_url_rewrite.product_id, core_url_rewrite.request_path FROM core_url_rewrite WHERE (store_id = 1) AND (is_system = 1) AND (category_id = 2 OR category_id IS NULL) AND (product_id IN('2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '29', '30', '31', '33', '34', '35', '38', '39', '40', '41', '44', '45', '46', '47', '48', '49', '50', ... , '3077897')) ORDER BY category_id DESC

magento seems to generate a sql query with IN() and all of the product ids. This very big query seems to be too much for mysql.

This query is also executed, when I want to delete a product image (catalogProductAttributeMediaRemove), which makes no sense, because it has nothing todo with url rewriting.

It worked fine before upgrading to version (from 1.9.1),

Is this behaviour normal? Is there a way to use url_rewrite with 2.000.000 products and the last version of magento?

  • You have 2 million URL Rewrites? How many SKU and Categories? – brentwpeterson Aug 19 '15 at 17:19
  • we have over 5 million entries in core_url_rewrite, nearly 3 million sku and 1100 categories. – Philip Faltin Aug 19 '15 at 19:17
  • And it works if you go back to version 1.9.1? – brentwpeterson Aug 19 '15 at 19:33
  • now it's the same in 1.9.1. I also tried several mysql version: 5.6.25, 5.5.44 and 5.5.43. It's always starting this inefficient query. Also in soap querys that don't change the product title or url like changing images. Only soap querys regarding orders are working fine. – Philip Faltin Aug 20 '15 at 0:27
  • I found a similar problem on the web, but there was posted no solutions. It was from 2012 and regarding Magento Enterprise 1.12: forum.azmagento.com/how-to/… – Philip Faltin Aug 20 '15 at 0:36

I found a solution to my problem:

After removing all products from the root category the sql problem was gone and the soap connection worked, but very very slow (10 minutes for 100 updates using soap v1 multicall with all indexes disabled).

I tried a lot within the last days, nothing helped.

I'm using varnish cache (running on another physical server). Today I disabled varnish and suddenly my soap connection was running as fast as it was bevor upgrading to I checked the varnish options in magento and disabled "Enable Site Crawler". I remember: it was disabled before upgrading. Perhaps I enabled it when upgrading :-(

Now my SOAP connection is as fast as in former days.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.