MySQL eval()

September 6, 2011

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.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

10 Comments to "MySQL eval()"

  1. common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day() | code.openark.org wrote:

    [...] MySQL eval() [...]

  2. Giuseppe Maxia wrote:

    Here's a poor man's eval():

    mysql> pager tail -n 2 | head -n 1 | tr '|' ' ' | mysql -u user -pPWD

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

    mysql> nopager

  3. shlomi wrote:

    @Giuseppe,
    Cool.
    I think SELECT INTO OUTFILE + SOURCE are less scary. The problem with both solution is that they require interactive mysql client (SOURCE is a client command; pager is a client command).

    The solution offered in eval() is entirely on the server.

  4. Giuseppe Maxia wrote:

    @Shlomi,
    Noted. I did a server-side eval() on steroids several years ago (http://datacharmer.blogspot.com/2005/12/mysql-5-general-purpose-routine.html).
    This poor version is client-side, but it doesn't require installation.

  5. shlomi wrote:

    Have just looked at the source code for mysql-sr-lib.
    Wow! What an effort!

    However this is strange. I'm quite sure a couple years back some DDL were not supported with prepared statements (e.g. DROP, ANALYZE...) and yet I see you used them.

    Perhaps they were first enabled, then disabled, then re-enabled again?

  6. shlomi wrote:

    Giuseppe,
    "This poor version is client-side, but it doesn't require installation."
    I strive to world domination: there shall be no MySQL installation that does not include common_schema. It shall be the de facto utility schema for MySQL.

    I just need to get the funds for the PR of course.

  7. Giuseppe Maxia wrote:

    Shlomi,
    world domination is a commendable goal.
    If you add loops and user variables to your routines, I will become an enthusiastic proponent.
    Then you won;t need PR funds, but you will relay on the force of the community.

  8. shlomi wrote:

    Ahhh, the force!

  9. Quoting text JavaScript/Python style | code.openark.org wrote:

    [...] MySQL eval() [...]

  10. MySQL foreach() | code.openark.org wrote:

    [...] MySQL eval() [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org