MySQL query timeout: (70100): Query was aborted

Question

I want to execute a MySQL query to delete all rows of the Wp_posts table that post_parent represents the Wp_posts row with post_type installed in the product;

So i do

 INSERT INTO temp (SELECT DISTINCT id FROM wp_posts WHERE post_type = "product") 

(Inserts 4k lines)

 DELETE FROM wp_posts WHERE post_parent IN (SELECT tid FROM temp) 

..

After about 100 seconds, it returns

ERROR 1317 (70100): Request was aborted

What could make this request so bleeding?

Answer 1

Some versions of MySQL implement in with a subquery in very inefficient mode. Change it to a correlated exists clause:

 DELETE FROM wp_posts WHERE exists (SELECT 1 FROM temp where temp.tid = wp_posts.post_parent) 

Answer 2

You need to change your max_allowed_packet to a higher value in the my.ini configuration file.

This can help:

Too big package

The communication package is a single SQL statement sent to the MySQL server, a single line sent to the client, or a binary log event sent from the main replication server to the subordinate.

The largest package that can be transferred to a MySQL server or MySQL 5.5 client server is 1 GB.