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.