run a mysql maintenance command until no rows are changed

TL;DR:

SET @quit=IF(ROW_COUNT()=0,'QUIT','SELECT 0 quit'); PREPARE quit FROM @quit; EXECUTE quit;

Detail: Sometimes you want to delete records older than 45 days from a table:

DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;

But you don’t want to make a huge long-running transaction of it, so you do it in chunks of LIMIT 1000:

DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day LIMIT 1000;

But you want to do more than 1 chunk, so you make a repetitive script:

DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day;

But 5 times is likely wrong: we need to quit when the number of rows affected is zero, ie. ROW_COUNT()=0 : but MySQL doesn’t have conditional execution in the comand line interface.

We can fudge it: here’s the recipe, to run from the shell:

yes "
DELETE FROM livetable WHERE acctstarttime < now()-interval 45 day LIMIT 1000;
SET @quit=IF(ROW_COUNT()=0,'QUIT','SELECT 0 quit'); PREPARE quit FROM @quit; EXECUTE quit;
" | mysql livedatabase

The yes command prints out the string over and over, and the line that says EXECUTE runs a string which makes an error (QUIT is not a mysql command), and the error causes mysql to quit.