{"id":3952,"date":"2011-09-06T09:05:34","date_gmt":"2011-09-06T07:05:34","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3952"},"modified":"2011-09-06T09:05:34","modified_gmt":"2011-09-06T07:05:34","slug":"common_schema-rev-68-eval-processlist_grantees-candidate_keys-easter_day","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/common_schema-rev-68-eval-processlist_grantees-candidate_keys-easter_day","title":{"rendered":"common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()"},"content":{"rendered":"<p>Revision <strong>68<\/strong> of <a rel=\"nofollow\" href=\"http:\/\/code.google.com\/p\/common-schema\/\">common_schema<\/a> is out, and includes some interesting features:<\/p>\n<ul>\n<li><strong>eval()<\/strong>: Evaluates the queries generated by a given query<\/li>\n<li><strong>match_grantee()<\/strong>: Match an existing account based on user+host<\/li>\n<li><strong>processlist_grantees<\/strong>: Assigning of GRANTEEs for connected processes<\/li>\n<li><strong>candidate_keys<\/strong>: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.<\/li>\n<li><strong>easter_day()<\/strong>: Returns DATE of easter day in given DATETIME&#8217;s year.<\/li>\n<\/ul>\n<p>Let&#8217;s take a slightly closer look at these:<\/p>\n<h4>eval()<\/h4>\n<p>I&#8217;ve dedicated this blog post on <a href=\"http:\/\/code.openark.org\/blog\/mysql\/mysql-eval\">MySQL eval()<\/a> to describe it. In simple summary: <strong>eval()<\/strong> takes a query which generates queries (most common use queries on <strong>INFORMATION_SCHEMA<\/strong>) and auto-evaluates (executes) those queries. <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/general_procedures.html#eval\">Read more<\/a><\/p>\n<h4>match_grantee()<\/h4>\n<p>As presented in <a title=\"Link to Finding CURRENT_USER for any user\" rel=\"bookmark\" href=\"http:\/\/code.openark.org\/blog\/mysql\/finding-current_user-for-any-user\">Finding CURRENT_USER for any user<\/a>, I&#8217;ve developed the algorithm to match a connected user+host details (as presented with <strong>PROCESSLIST<\/strong>) with the grantee tables (i.e. the <strong>mysql.user<\/strong> table), in a manner which simulates the MySQL server account matching algorithm.<\/p>\n<p>This is now available as a stored function: given a user+host, the function returns with the best matched grantee. <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/privileges_functions.html#match_grantee\">Read more<\/a><\/p>\n<h4>processlist_grantees<\/h4>\n<p>This view relies on the above, and maps the entire <strong>PROCESSLIST<\/strong> onto GRANTEEs. The view maps each process onto the GRANTEE (MySQL account) which is the owner of that process. Surprisingly, MySQL does not provide one with such information.<!--more--><\/p>\n<p>The view also provides with the following useful metadata:<\/p>\n<ul>\n<li>Is said process executes under a SUPER privilege?<\/li>\n<li>Is this a replication thread, or serving a replicating client?<\/li>\n<li>Is this process the current connection (myself)?<\/li>\n<\/ul>\n<p>In the spirit of <strong>common_schema<\/strong>, it provides with the SQL commands necessary to <strong>KILL<\/strong> and <strong>KILL QUERY<\/strong> for each process. A sample output:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT * FROM common_schema.processlist_grantees;\r\n+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+\r\n| ID\u00a0\u00a0\u00a0\u00a0 | USER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | HOST\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | GRANTEE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | grantee_user | grantee_host | is_super | is_repl | sql_kill_query\u00a0\u00a0\u00a0 | sql_kill_connection |\r\n+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+\r\n| 650472 | replica\u00a0\u00a0\u00a0 | jboss00.myweb:34266 | 'replica'@'%.myweb'\u00a0\u00a0\u00a0 | replica\u00a0\u00a0\u00a0\u00a0\u00a0 | %.myweb\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 | KILL QUERY 650472 | KILL 650472\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 692346 | openarkkit | jboss02.myweb:43740 | 'openarkkit'@'%.myweb' | openarkkit\u00a0\u00a0 | %.myweb\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 | KILL QUERY 692346 | KILL 692346\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 842853 | root\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | localhost\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 'root'@'localhost'\u00a0\u00a0\u00a0\u00a0 | root\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | localhost\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 | KILL QUERY 842853 | KILL 842853\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 843443 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss03.myweb:40007 | 'jboss'@'%.myweb'\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | %.myweb\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 | KILL QUERY 843443 | KILL 843443\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 843444 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss03.myweb:40012 | 'jboss'@'%.myweb'\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | %.myweb\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 | KILL QUERY 843444 | KILL 843444\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 843510 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss00.myweb:49850 | 'jboss'@'%.myweb'\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | %.myweb\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 | KILL QUERY 843510 | KILL 843510\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| 844559 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss01.myweb:37031 | 'jboss'@'%.myweb'\u00a0\u00a0\u00a0\u00a0\u00a0 | jboss\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | %.myweb\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 | KILL QUERY 844559 | KILL 844559\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+<\/pre>\n<\/blockquote>\n<p>Finally, it is now possible to execute the following:\u00a0 \u201cKill all slow queries which are not executed by users with the SUPER privilege or are replication threads\u201d. To just generate the commands, execute:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT <strong>sql_kill_connection<\/strong> FROM <strong>common_schema.processlist_grantees<\/strong> WHERE is_super = 0 AND is_repl = 0;<\/pre>\n<\/blockquote>\n<p>Sorry, did you only want to kill the queries? Those which are very slow? Do as follows:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT sql_kill_connection FROM common_schema.processlist_grantees JOIN INFORMATION_SCHEMA.PROCESSLIST <strong>USING(ID)<\/strong> WHERE <strong>TIME &gt; 10<\/strong> AND is_super = 0 AND is_repl = 0;<\/pre>\n<\/blockquote>\n<p>But, really, we don&#8217;t just want <em>commands<\/em>. We really want to execute this!<\/p>\n<p>Good! Step in <strong>eval()<\/strong>:<\/p>\n<blockquote>\n<pre>mysql&gt; CALL common_schema.<strong>eval<\/strong>('SELECT <strong>sql_kill_query<\/strong> FROM common_schema.processlist_grantees JOIN INFORMATION_SCHEMA.PROCESSLIST USING(id) WHERE TIME &gt; 10 AND is_super = 0 AND is_repl = 0');<\/pre>\n<\/blockquote>\n<p><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/processlist_grantees.html\">Read more<\/a><\/p>\n<h4>candidate_keys<\/h4>\n<p>A view which lists the candidate keys for tables and provides ranking for those keys, based on some simple heuristics.<\/p>\n<p>This view uses\u00a0 the same algorithm as that used by <a href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-chunk-update.html\">oak-chunk-update<\/a> and <a href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-online-alter-table.html\">oak-online-alter-table<\/a>, tools in the <a href=\"http:\/\/code.openark.org\/forge\/openark-kit\">openark kit<\/a>. So it provides with a way to choose the best candidate key to walk through a table. At current, a table&#8217;s <strong>PRIMARY KEY<\/strong> is always considered to be best, because of InnoDB&#8217;s structure of clustered index. But I intend to change that as well and provide general recommendation about candidate keys (so for example, I would be able to recommend that the <strong>PRIMARY KEY<\/strong> is not optimal for some table).<\/p>\n<p>Actually, after a discussion initiated by Giuseppe and Roland, starting <a href=\"http:\/\/datacharmer.blogspot.com\/2011\/09\/finding-tables-without-primary-keys.html\">here<\/a> and continuing on mail, there are more checks to be made for candidate keys, and I suspect the next version of <em>candidate_keys<\/em> will be more informational.<\/p>\n<p><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/candidate_keys.html\">Read more<\/a><\/p>\n<h4>easter_day()<\/h4>\n<p>Many thanks to <a href=\"http:\/\/rpbouman.blogspot.com\/\">Roland Bouman<\/a> who suggested his code for calculating easter day for a given year. <em>Weehee!<\/em> This is the first contribution to <em>common_schema<\/em>! <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/time_functions.html#easter_day\">Read more<\/a><\/p>\n<h4>Get it<\/h4>\n<p><em>common_schema<\/em> is an open source project. It is released under the BSD license.<\/p>\n<p><a href=\"http:\/\/code.google.com\/p\/common-schema\/\">Find it here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Revision 68 of common_schema is out, and includes some interesting features: eval(): Evaluates the queries generated by a given query match_grantee(): Match an existing account based on user+host processlist_grantees: Assigning of GRANTEEs for connected processes candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use. easter_day(): Returns DATE of easter day [&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,26,57,16],"class_list":["post-3952","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-indexing","tag-open-source","tag-security"],"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\/3952","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=3952"}],"version-history":[{"count":31,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3952\/revisions"}],"predecessor-version":[{"id":3995,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3952\/revisions\/3995"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3952"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3952"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3952"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}