Skip to content

# MySQL eval()

MySQL common_schema SQL Stored routines 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
10
Leave a Reply

10 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day() | code.openark.org

[…] MySQL eval() […]

Guest
Giuseppe Maxia

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

Author
shlomi

@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.

Guest
Giuseppe Maxia

@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.

Author
shlomi

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?

Author
shlomi

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.

Guest
Giuseppe Maxia

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.

Author
shlomi

Ahhh, the force!

Quoting text JavaScript/Python style | code.openark.org

[…] MySQL eval() […]

MySQL foreach() | code.openark.org

[…] MySQL eval() […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.