common_schema 1.2: security, partition management, processes, QueryScript goodies

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 info and SQL statements in processlist-related views

A closer look at these follows:

Account blocking

A new view called sql_accounts, inspired by oak-block-account (also see here and here) 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 killall() and sql_accounts this gives the administrator great control over accounts.

Security audit

Imported from openark kit, and implemented via QueryScript, the security_audit() procedure will audit your accounts, passwords and general settings to find problems, pitfalls and security hazards. I will write more on this later.

RANGE partition management

The sql_range_partitions view manages your RANGE and RANGE COLUMNS partitioned tables by providing with the SQL statements to drop oldest partitions and to create the next (in sequence) partitions. See my earlier post.

Slave status

This is a hack providing a minified version of SHOW SLAVE STATUS, but as a view (slave_status). It only provides with 5 columns:

mysql> SELECT * FROM slave_status \G
*************************** 1. row ***************************
 Slave_Connected_time: 82077
     Slave_IO_Running: 1
    Slave_SQL_Running: 1
        Slave_Running: 1
Seconds_Behind_Master: 5

For me, the Seconds_Behind_Master is one critical value I am interested in getting using a query. So here it is.

mysql> SELECT (Seconds_Behind_Master < 10) IS TRUE AS slave_is_up_to_date FROM slave_status;
+---------------------+
| slave_is_up_to_date |
+---------------------+
|                   1 |
+---------------------+

QueryScript goodies

• while-otherwise statement: while (some_condition) { … } otherwise { /* this gets executed if the while never performs a single iteration */ }
• foreach-otherwise statement, likewise
• echo, report 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).
• 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.
• Complete variable expansion handling: expanded variables are now recognized anywhere within the script, including inside a while or foreach expression.
• Transactions are now handled by QueryScript and immediately delegated to MySQL. This completes the transaction management in QueryScript. Just start transaction, commit or rollback at will.

InnoDB idle transactions, blocking transactions

The innodb_transactions 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.

The innodb_locked_transactions view now offers the wait time and SQL statements for killing the query or connection of a blocking  transaction. This allows for a quick track or track-and-kill long time blocking transactions.

I will write more in depth on both in a future post.

Processlist-related views

The new processlist_states view aggregates processlist by thread state. This view, and all other processlist views now provide with median or 95% median runtime for processes, in addition to the less informative AVG provided earlier.

Get it!

common_schema 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. common_schema installs on any MySQL >= 5.1 server, including Percona Server and MariaDB, tested on 5.1, 5.5 and 5.6 RC.

Go to common_schema download page.