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

Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):

mysql> SELECT * FROM sql_range_partitions \G
*************************** 1. row ***************************
            table_schema: test
              table_name: city
sql_drop_first_partition: alter table `test`.`city` drop partition `p3`
  sql_add_next_partition: alter table `test`.`city` add partition (partition `p_20160101000000` values less than (736329) /* 2016-01-01 00:00:00 */ )
*************************** 2. row ***************************
            table_schema: test
              table_name: quarterly_report_status
sql_drop_first_partition: alter table `test`.`quarterly_report_status` drop partition `p3`
  sql_add_next_partition: alter table `test`.`quarterly_report_status` reorganize partition `p_maxvalue` into (partition `p_20110401000000` values less than (1301608800) /* 2011-04-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE)

A closer look at why this is magic

This query just gave you the DROP PARTITION and ADD PARTITION for all tables in your databases that use a RANGE partitioning scheme. But, consider:

• The query automatically deduces the LESS THAN value of the new partition. It looks for a constant interval, time-based or integer-based, and keeps this interval onward.
• It understands that 5.1 does not allow you to partition by DATETIME, only via integers. It understands your integer may sometimes stand for TO_DAYS(), and sometimes for UNIX_TIMESTAMP() of your datetime. It auto-detects that.
• The query recognizes a MAXVALUE partition, and if such partition exists, it provides with a REORGANIZE PARTITION statement rather than ADD PARTITION statement.
• It suggests names for your partitions which give you a clue on what the partition contains. p_20160101000000 (can you splot the date/time?) tells me a lot more than some arbitrary p17.
• It recognizes the common case of using a LESS THAN (0) as first partition, to take care of NULLs. It skips this partition: the query does not offer to drop it, not does it consider it while examining the interval.

So I never have to tell the query “I want a 3 month interval between partitions, and these are implemented using TO_DAYS()”. I let it understand it on its own.

It is just a view

Which means you can SELECT sql_drop_first_partition, or you can SELECT sql_add_next_partition, or you can only SELECT … WHERE table_schema=’your_schema’. Or you can select them all.

You can eval() it

This view will be released with common_schema‘s next version. common_schema has a lot of these views which generate SQL statements. And it provides with the means to evaluate them: the eval() routine. So you don’t need to export the text INTO OUTFILE and execute it via SOURCE. You can simply:

call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");

And it is done.

Get it

The sql_range_partitions view will be included in common_schema 1.2, schedules to be released soon. Meanwhile, you can import this file: [download id=”1″ format=”1″] onto your existing common_schema 1.1 install (what? You don’t already have common_schema installed? You should know it’s packed with lots of stuff like this one!)

If, by the time you read this, common_schema 1.2 is already out, you don’t need to add anything.

In action

Consider the following table on a MySQL 5.1 server:

CREATE TABLE test.quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL 
)
PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (
    PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2008-01-01 00:00:00')),
    PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2008-04-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2008-07-01 00:00:00')),
    PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2008-10-01 00:00:00')),
    PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2009-01-01 00:00:00')),
    PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2009-04-01 00:00:00')),
    PARTITION p6 VALUES LESS THAN (MAXVALUE)
);

Unfortunately MySQL does not remember the definition expressions, so:

mysql> SHOW CREATE TABLE test.quarterly_report_status \G

Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated))
(PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM,
 PARTITION p6 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Yikes! What does 1238533200 stand for?

No worries, let’s eval():

mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");

mysql> SHOW CREATE TABLE test.quarterly_report_status \G

CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated))
(PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM,
 PARTITION p_20090701000000 VALUES LESS THAN (1246395600) ENGINE = MyISAM,
 PARTITION p_maxvalue VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Our query auto-detected the meaning of those numbers like 1238533200, and has found the next partition to be created: p_20090701000000. That’s ‘2009-07-01 00:00:00’, and now we know what the partition stands for. A new MAXVALUE partition called p_maxvalue is created.

Just for the fun of it, let’s issue the same a few more times and see what comes out:

mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");

mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");

mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");

mysql> call common_schema.eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");

mysql> SHOW CREATE TABLE test.quarterly_report_status \G

Create Table: CREATE TABLE `quarterly_report_status` (
  `report_id` int(11) NOT NULL,
  `report_status` varchar(20) NOT NULL,
  `report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated))
(PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM,
 PARTITION p_20090701000000 VALUES LESS THAN (1246395600) ENGINE = MyISAM,
 PARTITION p_20091001000000 VALUES LESS THAN (1254348000) ENGINE = MyISAM,
 PARTITION p_20100101000000 VALUES LESS THAN (1262296800) ENGINE = MyISAM,
 PARTITION p_20100401000000 VALUES LESS THAN (1270069200) ENGINE = MyISAM,
 PARTITION p_20100701000000 VALUES LESS THAN (1277931600) ENGINE = MyISAM,
 PARTITION p_maxvalue VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Notice the number interval is not constant: we have different number of days in different quarters. We have Feb 29th every 4 years. Yet we get the right LESS THAN value.

Now isn’t this cool?