Magento lost connection to mysql server during query on deletion of 50000 products

Question

I am deleting 50000 products from Magento using MySql query but every time I am getting timeout error.

 lost connection to mysql server during query

Is there any way to fix this? I have already increased the max_execution_time in the php.ini.

I have also set max_allowed_packet to 64M

I am using following command to delete the products.

DELETE FROM catalog_product_entity WHERE entity_id IN (here I am providing the entity_ids with comma separated)

DELETE FROM catalog_product_entity WHERE entity_id IN ('1','2','3',.......'5000')

Is there any other way of deleting the products safely without time out?

Note : will this command help me speed up the application

mysql -u root -pYOURPASSWORD -e "flush query cache";

I have set this to run after every 9 minutes using cronjob

My database size is very huge, more than 50GB and server is dedicated server with 16 GB RAM.

I am running this query directly in phpmyadmin.

Entities are in random order

Is the entity_id in consecutive order? Can you do some think like `where entity_id NOT IN (1,3) and entity_id <= 5000` — MagePal Extensions, May 27 '14 at 16:01
Take a look @ http://stackoverflow.com/questions/4771035/mysql-query-in-clause-slow-on-indexed-column — MagePal Extensions, May 27 '14 at 16:24

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

A couple thoughts depending on your situation:

1) Are you using a persistent DB connection? Are tables being used/locked during your query? Look at mysql_pconnect mysql_pconnect — Open a persistent connection to a MySQL server

2) Maybe you just need to adjust the default timeout for MySQL? Look here on How to Change the MySQL Timeout on a Server

3) There is also a good answer here which may be your issue since it's such a large query: Lost connection to MySQL server during query

edited May 23 '17 at 11:50

Community

answered May 27 '14 at 16:07

gtr1971

I am running this query directly in phpmyadmin. – Mukesh May 27 '14 at 18:14
Then option #2 or #3 may apply. READ the information at those links! – gtr1971 May 27 '14 at 18:28

1 Answers1