{"id":3926,"date":"2011-09-06T09:02:42","date_gmt":"2011-09-06T07:02:42","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3926"},"modified":"2011-09-06T09:02:42","modified_gmt":"2011-09-06T07:02:42","slug":"mysql-eval","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-eval","title":{"rendered":"MySQL eval()"},"content":{"rendered":"<p>I&#8217;ve just implemented an <strong>eval()<\/strong> 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.<\/p>\n<h4>Just what is an eval() call?<\/h4>\n<p>In some programming languages it would mean: get some text, and execute it as though it were complied code. So, dynamic coding.<\/p>\n<p>In SQL: get the text of query which generates SQL statements in itself (either DML or DDL), and invoke those implied SQL statements.<\/p>\n<h4>A simple example<\/h4>\n<p>Best if I present <a href=\"http:\/\/www.mysqlperformanceblog.com\/2009\/05\/21\/mass-killing-of-mysql-connections\/\">Mass killing of MySQL Connections<\/a> by Peter Zaitsev. The thing is to execute a query, typically on <strong>INFORMATION_SCHEMA<\/strong>, which uses metadata so as to generate SQL queries\/commands. Peter&#8217;s example is:<\/p>\n<blockquote>\n<pre>select concat('KILL ',id,';') from information_schema.processlist where user='root'<\/pre>\n<\/blockquote>\n<p>The above query generates <strong>KILL<\/strong> commands for all users called <strong>&#8216;root&#8217;<\/strong>. I do many such queries in <a href=\"http:\/\/code.google.com\/p\/common-schema\/\" rel=\"nofollow\">common_schema<\/a>: like <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_show_grants.html\">creating the <strong>GRANT<\/strong> statements<\/a> for accounts, the <strong>DROP KEY<\/strong> statements for <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/redundant_keys.html\">redundant keys<\/a>, the <strong>ADD<\/strong> and <strong>DROP<\/strong> statements for <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_foreign_keys.html\">foreign keys<\/a> etc.<\/p>\n<p>So the problem is you have to export those statements to file, then execute them from file: either using <strong>SOURCE<\/strong>, as in Peter&#8217;s example, or from shell prompt, piping file contents into <strong>mysql<\/strong> client.<\/p>\n<h4>You can now eval()<\/h4>\n<p>I&#8217;ve been on family holiday for a couple of weeks, which meant no need to think of work. Which means <a href=\"http:\/\/code.openark.org\/blog\/mysql\/you-know-youve-been-doing-too-much-sql-when\">more time to think of SQL<\/a> (darn!). And I&#8217;ve found the way to do it completely within the server (no external files required).<!--more--><\/p>\n<p>A usage example of <strong>eval()<\/strong> is this:<\/p>\n<blockquote>\n<pre>CALL eval('select concat(\\'KILL \\',id) from information_schema.processlist where user=\\'root\\'');<\/pre>\n<\/blockquote>\n<p>Note the changes:<\/p>\n<ul>\n<li>You pass your query as a string<\/li>\n<li>Consequently, you have to escape it<\/li>\n<li>No terminating <strong>&#8216;;&#8217;<\/strong> required<\/li>\n<li>You sit back and relax<\/li>\n<\/ul>\n<p>The <strong>eval()<\/strong> procedure will:<\/p>\n<ul>\n<li>Execute the given query<\/li>\n<li>Expect it to return with exactly one textual column<\/li>\n<li>Expect text results to be valid queries or commands (i.e. UPDATE, DROP, KILL, ALTER, &#8230;)<\/li>\n<li>Execute, in turn, each one of those statements<\/li>\n<\/ul>\n<p>It will also require a temporary table. Thus, the user invoking <strong>eval()<\/strong> must have the <strong>CREATE TEMPORARY TABLES<\/strong> privilege, as well as any other privilege required for the supplied and implied queries. One typically executes <strong>eval()<\/strong> queries as a super user (e.g. <strong>&#8216;root&#8217;<\/strong> account), so this should not be a problem.<\/p>\n<p>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.<\/p>\n<h4>An execution example<\/h4>\n<p>Let&#8217;s kill all queries running for more than <strong>20<\/strong> seconds, shall we?<\/p>\n<blockquote>\n<pre>root@mysql-5.1.51&gt; SHOW PROCESSLIST;\r\n+----+------+-----------+---------------+---------+------+------------+---------------------+\r\n| Id | User | Host\u00a0\u00a0\u00a0\u00a0\u00a0 | db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Command | Time | State\u00a0\u00a0\u00a0\u00a0\u00a0 | Info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------+-----------+---------------+---------+------+------------+---------------------+\r\n|\u00a0 2 | root | localhost | common_schema | Query\u00a0\u00a0 |\u00a0\u00a0\u00a0 0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | SHOW PROCESSLIST\u00a0\u00a0\u00a0 |\r\n| 43 | apps | localhost | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Query\u00a0\u00a0 |\u00a0\u00a0 28 | User sleep | select sleep(10000) |\r\n+----+------+-----------+---------------+---------+------+------------+---------------------+\r\n2 rows in set (0.00 sec)\r\n\r\nroot@mysql-5.1.51&gt; <strong>CALL eval('SELECT CONCAT(\\'KILL \\',id) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME &gt; 20');<\/strong>\r\nQuery OK, 0 rows affected (0.01 sec)\r\n\r\nroot@mysql-5.1.51&gt; SHOW PROCESSLIST;\r\n+----+------+-----------+---------------+---------+------+-------+------------------+\r\n| Id | User | Host\u00a0\u00a0\u00a0\u00a0\u00a0 | db\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Command | Time | State | Info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------+-----------+---------------+---------+------+-------+------------------+\r\n|\u00a0 2 | root | localhost | common_schema | Query\u00a0\u00a0 |\u00a0\u00a0\u00a0 0 | NULL\u00a0 | SHOW PROCESSLIST |\r\n+----+------+-----------+---------------+---------+------+-------+------------------+\r\n1 row in set (0.00 sec)<\/pre>\n<\/blockquote>\n<h4>Where is it?<\/h4>\n<p>It&#8217;s part of <a href=\"http:\/\/code.google.com\/p\/common-schema\/\" rel=\"nofollow\">common_schema<\/a>, and I&#8217;m suggesting <em>common_schema<\/em> should appeal to anyone using or administrating a MySQL instance. There is a lot to this project in useful views &amp; routines which help you out in analyzing and diagnosing your server, providing you with features the MySQL server itself does not provide.<\/p>\n<p>If you just wish to browse <strong>eval()<\/strong>&#8216;s source code, <a href=\"http:\/\/code.google.com\/p\/common-schema\/source\/browse\/trunk\/common_schema\/routines\/general\/eval.sql\">it&#8217;s here<\/a>. <strong>eval()<\/strong>&#8216;s documentation is <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/eval.html\">here<\/a>, though if you&#8217;ve read thus far, you&#8217;re covered.<\/p>\n<p>An announcement on a new <em>common_schema<\/em> release will follow.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[67,21,59],"class_list":["post-3926","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-sql","tag-stored-routines"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-11k","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3926","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3926"}],"version-history":[{"count":39,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3926\/revisions"}],"predecessor-version":[{"id":4497,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3926\/revisions\/4497"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3926"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3926"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3926"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}