{"id":5539,"date":"2012-11-13T14:25:38","date_gmt":"2012-11-13T12:25:38","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5539"},"modified":"2012-11-13T14:25:38","modified_gmt":"2012-11-13T12:25:38","slug":"common_schema-1-2-security-partition-management-processes-queryscript-goodies","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/common_schema-1-2-security-partition-management-processes-queryscript-goodies","title":{"rendered":"common_schema 1.2: security, partition management, processes, QueryScript goodies"},"content":{"rendered":"<p><a href=\"http:\/\/code.google.com\/p\/common-schema\">common_schema<\/a> <strong>1.2<\/strong> is released! This version comes shortly after <strong>1.1<\/strong>, yet contains quite a few interesting goodies:<\/p>\n<ul>\n<li>Account blocking<\/li>\n<li>Security audit<\/li>\n<li>RANGE partition management<\/li>\n<li>Slave status<\/li>\n<li>Better blocking and idle transaction management<\/li>\n<li><em>QueryScript <\/em>goodies:\n<ul>\n<li>echo, report<\/li>\n<li>while-otherwise statement; foreach-otherwise statement<\/li>\n<li>Better variable scope handling<\/li>\n<li>Complete support for variable expansion<\/li>\n<li>Transaction support within QueryScript<\/li>\n<\/ul>\n<\/li>\n<li>More summary info and SQL statements in processlist-related views<\/li>\n<\/ul>\n<p>A closer look at these follows:<\/p>\n<h4>Account blocking<\/h4>\n<p>A new view called <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_accounts.html\"><strong>sql_accounts<\/strong><\/a>, inspired by <a href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-block-account.html\">oak-block-account<\/a> (also see <a href=\"http:\/\/code.openark.org\/blog\/mysql\/blocking-user-accounts\">here<\/a> and <a href=\"http:\/\/code.openark.org\/blog\/mysql\/pop-quiz-what-is-the-most-basic-privilege-an-account-can-be-assigned-with\">here<\/a>) provides with the means of blocking use accounts (and releasing them, of course) without revoking their privileges. It offers the SQL statements to block an account (by modifying its password in a symmetric way) and to release an account (by modifying its password back to normal). It really works like a charm. Together with <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/killall.html\">killall()<\/a> and <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_grants.html\">sql_accounts<\/a> this gives the administrator great control over accounts.<\/p>\n<h4>Security audit<\/h4>\n<p>Imported from <a href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-security-audit.html\">openark kit<\/a>, and implemented via <em>QueryScript<\/em>, the <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/security_audit.html\"><strong>security_audit()<\/strong><\/a> procedure will audit your accounts, passwords and general settings to find problems, pitfalls and security hazards. I will write more on this later.<\/p>\n<h4>RANGE partition management<\/h4>\n<p>The <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/sql_range_partitions.html\"><strong>sql_range_partitions<\/strong><\/a> view manages your <strong>RANGE<\/strong> and <strong>RANGE COLUMNS<\/strong> partitioned tables by providing with the SQL statements to drop oldest partitions and to create the next (in sequence) partitions. See my <a href=\"http:\/\/code.openark.org\/blog\/mysql\/your-magical-range-partitioning-maintenance-query\">earlier post<\/a>.<!--more--><\/p>\n<h4>Slave status<\/h4>\n<p>This is a hack providing a minified version of <strong>SHOW SLAVE STATUS<\/strong>, but as a <em>view<\/em> (<a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/slave_status.html\"><strong>slave_status<\/strong><\/a>). It only provides with <strong>5<\/strong> columns:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT * FROM slave_status \\G\r\n*************************** 1. row ***************************\r\n Slave_Connected_time: 82077\r\n     Slave_IO_Running: 1\r\n    Slave_SQL_Running: 1\r\n        Slave_Running: 1\r\nSeconds_Behind_Master: 5<\/pre>\n<\/blockquote>\n<p>For me, the <strong>Seconds_Behind_Master<\/strong> is one critical value I am interested in getting using a query. So here it is.<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT <strong>(Seconds_Behind_Master &lt; 10) IS TRUE<\/strong> AS slave_is_up_to_date FROM <strong>slave_status<\/strong>;\r\n+---------------------+\r\n| slave_is_up_to_date |\r\n+---------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+---------------------+<\/pre>\n<\/blockquote>\n<h4>QueryScript goodies<\/h4>\n<ul>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_while.html\"><strong>while-otherwise<\/strong><\/a> statement: <strong>while (some_condition) { &#8230; } otherwise { \/* this gets executed if the while never performs a single iteration *\/ }<\/strong><\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_foreach.html\"><strong>foreach-otherwise<\/strong><\/a> statement, likewise<\/li>\n<li><a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_echo.html\"><strong>echo<\/strong><\/a>, <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/query_script_report.html\"><strong>report<\/strong><\/a> statements: echo your statements before applying them, or just echo your comments along the code. Generate a (beautified) report at the end of script execution (which is how security_audit() works).<\/li>\n<li>Better variable scopes: now allowing variables of same name to be declared when their scopes do not overlap. This makes for the expected behavior a programmer would expect.<\/li>\n<li>Complete variable expansion handling: expanded variables are now recognized anywhere within the script, including inside a while or <strong>foreach<\/strong> expression.<\/li>\n<li>Transactions are now handled by QueryScript and immediately delegated to MySQL. This completes the transaction management in QueryScript. Just <strong>start transaction<\/strong>, <strong>commit<\/strong> or <strong>rollback<\/strong> at will.<\/li>\n<\/ul>\n<h4>InnoDB idle transactions, blocking transactions<\/h4>\n<p>The <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/innodb_transactions.html\"><strong>innodb_transactions<\/strong><\/a> view now lists idle transactions, as well as their idle time. It also provides with the SQL statements to kill the query or connection for each transaction. This allows for a quick track or track-and-kill of idle transactions.<\/p>\n<p>The <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/innodb_locked_transactions.html\"><strong>innodb_locked_transactions<\/strong><\/a> view now offers the wait time and SQL statements for killing the query or connection of a blocking\u00a0 transaction. This allows for a quick track or track-and-kill long time blocking transactions.<\/p>\n<p>I will write more in depth on both in a future post.<\/p>\n<h4>Processlist-related views<\/h4>\n<p>The new <a href=\"http:\/\/common-schema.googlecode.com\/svn\/trunk\/common_schema\/doc\/html\/processlist_states.html\"><strong>processlist_states<\/strong><\/a> view aggregates processlist by thread state. This view, and all other processlist views now provide with median or <strong>95%<\/strong> median runtime for processes, in addition to the less informative AVG provided earlier.<\/p>\n<h4>Get it!<\/h4>\n<p><em>common_schema<\/em> is free and licensed under the New BSD License. It is nothing but a SQL file, so you simply import it into your MySQL server. <em>common_schema<\/em> installs on any MySQL &gt;= <strong>5.1<\/strong> server, including Percona Server and MariaDB, tested on <strong>5.1<\/strong>, <strong>5.5<\/strong> and <strong>5.6 RC<\/strong>.<\/p>\n<p><a href=\"http:\/\/code.google.com\/p\/common-schema\">Go to common_schema download page<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies: Account blocking Security audit RANGE partition management Slave status Better blocking and idle transaction management QueryScript goodies: echo, report while-otherwise statement; foreach-otherwise statement Better variable scope handling Complete support for variable expansion Transaction support within QueryScript More summary [&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,57,88,76,16],"class_list":["post-5539","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-common_schema","tag-open-source","tag-partitioning","tag-queryscript","tag-security"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1rl","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5539","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=5539"}],"version-history":[{"count":33,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5539\/revisions"}],"predecessor-version":[{"id":5782,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5539\/revisions\/5782"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}