MySQL eval()

I’ve just implemented an eval() call for MySQL. It is implemented with SQL, using a stored procedure. So this is not some plugin: you can use it from within your normal database server.

Just what is an eval() call?

In some programming languages it would mean: get some text, and execute it as though it were complied code. So, dynamic coding.

In SQL: get the text of query which generates SQL statements in itself (either DML or DDL), and invoke those implied SQL statements.

A simple example

Best if I present Mass killing of MySQL Connections by Peter Zaitsev. The thing is to execute a query, typically on INFORMATION_SCHEMA, which uses metadata so as to generate SQL queries/commands. Peter’s example is:

select concat('KILL ',id,';') from information_schema.processlist where user='root'

The above query generates KILL commands for all users called ‘root’. I do many such queries in common_schema: like creating the GRANT statements for accounts, the DROP KEY statements for redundant keys, the ADD and DROP statements for foreign keys etc.

So the problem is you have to export those statements to file, then execute them from file: either using SOURCE, as in Peter’s example, or from shell prompt, piping file contents into mysql client.

You can now eval()

I’ve been on family holiday for a couple of weeks, which meant no need to think of work. Which means more time to think of SQL (darn!). And I’ve found the way to do it completely within the server (no external files required).

A usage example of eval() is this:

CALL eval('select concat(\'KILL \',id) from information_schema.processlist where user=\'root\'');

Note the changes:

• You pass your query as a string
• Consequently, you have to escape it
• No terminating ‘;’ required
• You sit back and relax

The eval() procedure will:

• Execute the given query
• Expect it to return with exactly one textual column
• Expect text results to be valid queries or commands (i.e. UPDATE, DROP, KILL, ALTER, …)
• Execute, in turn, each one of those statements

It will also require a temporary table. Thus, the user invoking eval() must have the CREATE TEMPORARY TABLES privilege, as well as any other privilege required for the supplied and implied queries. One typically executes eval() queries as a super user (e.g. ‘root’ account), so this should not be a problem.

This solution was made possible due to the fact that you can now send DDL commands via prepared statements. This makes for a very dynamic and versatile was to generate and execute queries and commands.

An execution example

Let’s kill all queries running for more than 20 seconds, shall we?

root@mysql-5.1.51> 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)

root@mysql-5.1.51> CALL eval('SELECT CONCAT(\'KILL \',id) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 20');
Query OK, 0 rows affected (0.01 sec)

root@mysql-5.1.51> 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)

Where is it?

It’s part of common_schema, and I’m suggesting common_schema should appeal to anyone using or administrating a MySQL instance. There is a lot to this project in useful views & routines which help you out in analyzing and diagnosing your server, providing you with features the MySQL server itself does not provide.

If you just wish to browse eval()‘s source code, it’s here. eval()‘s documentation is here, though if you’ve read thus far, you’re covered.

An announcement on a new common_schema release will follow.