After spending a lot of time on this I failed. Well, I failed to do this how I wanted to. I wanted to create a loop of sort – set it off once, walk away and come back to an empty table. I tried numerous times. I’m not going to list all the code examples (as they do not work – at least for me) but they were along the lines of:
#
# DISCLAIMER: DONT USE THIS CODE. IT DOES NOT WORK!
#
set innodb_lock_wait_timeout=10000;
set autocommit=0;
DROP PROCEDURE IF EXISTS clean_table;
DELIMITER $$
CREATE PROCEDURE clean_table()
BEGIN
my_loop : LOOP
IF (ROW_COUNT() <= 0 ) THEN LEAVE my_loop; END IF;
DELETE FROM TABLE WHERE (id < 100000000)LIMIT 200000;
END LOOP;
END$$
DELIMITER ;
CALL clean_table();
set autocommit=1;
After spending more time than I could afford on this, I decided to use another tact. I guessed the timeouts in the previous attempt were caused by a setting somewhere. So I checked out the settings in MySQL Workbench and indeed – there is a timeout setting. I changed this default from 30 seconds to 86,400 seconds (a whole day).
This then enabled me to delete tens of millions of rows at a time – albeit with a considerable delay. I’m sure you would want to do this on a production server and if that ever came up I would defiantly want to look in again on SQL procedures. But for now this method works.
The battle may be lost. But the war is not yet over.