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.