eval
NAME
eval(): Evaluates the queries generated by a given query.TYPE
ProcedureDESCRIPTION
Evaluates the queries generated by a given query. Given sql_query is expected to be a SQL generating query. That is, it is expected to produce, when invoked, a single text column consisting of SQL queries. The column may contain one or more queries per row. If multiple queries provided, the semicolon (;) delimiter is expected to terminate each. The last query does not have to be terminated with a semicolon.
The eval() procedure will invoke said queries, and then invoke (evaluate) any of the resulting queries.
Invoker of this procedure must have the CREATE TEMPORARY TABLES privilege, as well as any privileges required for evaluating implied queries.
Many of common_schema's views include SQL columns: columns containing read-to-invoke statements. Consider processlist_grantees, redundant_keys, sql_accounts, sql_alter_table, sql_foreign_keys, sql_grants, sql_range_partitions and more. Thus, it is possible to invoke eval() directly on such views, see examples below.
common_schema offers alternatives to eval(), on popular use cases. In particular, see QueryScript's foreach statement for many operations that can be produced via INFORMATION_SCHEMA.TABLES.
QueryScript also offers the eval statement, built into the language, which uses exact same logic as this routine (and in fact relies on it).
SYNOPSIS
eval(sql_query TEXT CHARSET utf8) MODIFIES SQL DATA
Input:
- sql_query: a query which generates SQL queries to be evaluated. Must return with exactly one column.
- @common_schema_dryrun: when 1, queries are not executed, but rather printed.
- @common_schema_verbose: when 1, queries are verbosed.
EXAMPLES
In the following example we kill all connections executing queries for more than 20 seconds.
mysql> SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+------------+---------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------------+---------+------+------------+---------------------+
| 2 | root | localhost | common_schema | Query | 0 | NULL | SHOW PROCESSLIST |
| 43 | apps | localhost | NULL | Query | 28 | User sleep | select sleep(10000) |
+----+------+-----------+---------------+---------+------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> CALL eval('SELECT CONCAT(\'KILL \',id) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 20');
mysql> SHOW PROCESSLIST;
+----+------+-----------+---------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+---------------+---------+------+-------+------------------+
| 2 | root | localhost | common_schema | Query | 0 | NULL | SHOW PROCESSLIST |
+----+------+-----------+---------------+---------+------+-------+------------------+
1 row in set (0.00 sec)
mysql> CALL eval("SELECT sql_kill_query FROM processlist_grantees WHERE COMMAND != 'Sleep' AND TIME > 20 AND is_super = 0 AND is_repl = 0");
Automatically add partition to a RANGE partition table (see sql_range_partitions):
mysql> CALL eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
Block accounts for user 'gromit' (see sql_accounts):
mysql> CALL eval("SELECT sql_block_account FROM sql_accounts WHERE USER = 'gromit'");
Kill transactions idle for 30 seconds or more:
mysql> CALL eval("SELECT sql_kill_query FROM common_schema.innodb_transactions WHERE trx_idle_seconds >= 30");