repeat_exec
NAME
repeat_exec(): Repeatedly executes given query or queries until some condition holds.TYPE
ProcedureDESCRIPTION
This procedure repeats execution of query or queries, on a given interval. It terminates according to a given condition, which may be one of several types (see following), including dynamic calculation.
The procedure is essentially a repeat-until looping device. It is tailored to fit common use case scenarios, such as repeat-until no more rows are affected or repeat-until some time has passed. Use cases range from breaking down huge transactions to smaller ones, through load testing, to data access & manipulation simulation.
It calls upon exec() for query execution. Queries may be of varying types (DML, DDL, other commands). See exec().
Invoker of this procedure must have the privileges required for execution of given queries.
SYNOPSIS
repeat_exec(interval_seconds DOUBLE, execute_queries TEXT CHARSET utf8, stop_condition TEXT CHARSET utf8)
Input:
- interval_seconds: number of seconds to sleep between invocation of queries.
This value can be a floating point number, e.g. 0.1 indicates one-tenth of a second.
repeat_exec() begins with query execution, then follows on to sleeping. Once the stop_condition is met, no more sleeping is performed. - execute_queries: one or more queries to execute per loop iteration.
Queries are separated by semicolons (;). See exec() for details. - stop_condition: the condition by which the loop terminates. Can be in one
of several forms and formats:
- NULL: no stop condition. The loop is infinite.
- 0: The loop terminates when no rows are affected by query (if multiple queries specified, loop terminates when no rows are affected by last of queries). This is in particular useful for DELETE or UPDATE statements, see examples.
- A positive integer (1, 2, 3, ...): loop terminates after given number of iterations.
- Short time format (e.g. '30s', '45m', '2h'): loop terminates after specified time has passed. See shorttime_to_seconds() for more on short time format.
- a SELECT query: query is re-evaluated at the end of each iteration. Loop terminates when query evaluates to a TRUE value. The query must return with one single row and one single value.
- @common_schema_dryrun: when 1, queries are not executed, but rather printed.
- @common_schema_verbose: when 1, queries are verbosed.
- Whatever output the queries may produce.
EXAMPLES
DELETE all rows matching some condition. Break a potentially huge DELETE (e.g. 500,000 rows) into smaller chunks, as follows:
- sleep_time is 2 seconds
- execute_queries only deletes 1000 rows at a time
- stop_condition is set to 0, meaning the query terminates when no more rows are affected, i.e., all matching rows have been deleted.
mysql> call repeat_exec(2, 'DELETE FROM sakila.rental WHERE customer_id=7 ORDER BY rental_id LIMIT 1000', 0);
Make a 15 seconds random INSERT, UPDATE and DELETE access pattern:
mysql> call repeat_exec(0.01, 'UPDATE world.City SET Name=MD5(RAND()) WHERE id=FLOOR(RAND()*4000); INSERT INTO world.City (Name, Population) VALUES (MD5(RAND()), 0); DELETE FROM world.City WHERE id=FLOOR(RAND()*4000);', '15s');
Execute a query until some dynamic condition holds:
mysql> call repeat_exec(0.5, 'DELETE FROM sakila.rental WHERE customer_id=7 ORDER BY rental_id LIMIT 100', 'SELECT SUM(customer_id = 7) < SUM(customer_id = 3) FROM sakila.rental');