This is a long due maintenance release of openark-kit. This release includes bugfixes and some enhancements, mainly to oak-online-alter-table.

oak-online-alter-table Changes / bug fixes include:

• Support for keyword-named columns
• Use of FORCE INDEX due to lack of MySQL's ability for figure out the chunking key at all times
• --sleep-ratio option added; allows for sleep time proportional to execution time (as opposed to constant sleep time with --sleep)
• Support for chunk-retry (in case of deadlock) via --max-lock-retries)
• Fixed order of cleanup
• Fixed bug with verbose messages with non-integer

common_schema 1.3 is released and is available for download. New and noteworthy in this version:

• Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed
• duplicate_grantee(): copy+paste existing accounts along with their full set of privileges
• similar_grants: find which accounts share the

If you are familiar with both openark kit and common_schema, you'll notice I've incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript.

What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples.

I'm generally interested in pushing as much functionality into the MySQL server. When using an external script, one:

• Needs the right dependencies (OS, Perl/Python version, Perl/Python modules).
• Needs to provide with connection params,
• Needs to get acquainted with a lot

Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy.

You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server side scripting with QueryScript, offering a very simple syntax with no external scripting, dependencies and command line options.

I wish to present three cases of row deletion, with three different solutions. In all cases we assume some TIMESTAMP column

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

Up till common_schema version 1.1, the user would need to choose from distinct distribution files: an install compatible with MySQL 5.1, one compatible with InnoDB Plugin enabled servers, and one compatible with Percona Server. The difference between the three is the availability of certain INFORMATION_SCHEMA tables.

With 1.1, this is no longer the case: common_schema auto-detects the server and available feature set, and installs accordingly.

Wait, isn't common_schema just a SQL file?

Yes. It's not like there's an installer like InstallShield or anything. Nevertheless, common_schema offers a smart way of conditional handling, which is uses in itself. It's called

This post exposes some of the internals, and the SQL behind QueryScript's split. common_schema/QueryScript 1.1 introduces the split statement, which auto-breaks a "large" query (one which operates on large tables as a whole or without keys) into smaller queries, and executes them in sequence.

This makes for easier transactions, less locks held, potentially (depending on the user) more idle time released back to the database. split has similar concepts to oak-chunk-update and

(pun intended)

common_schema's new split statement (see release announcement) auto-splits complex queries over large tables into smaller ones: instead of issuing one huge query, split breaks one's query into smaller queries, each working on a different set of rows (a chunk).

Thus, it is possible to avoid holding locks for long times, allowing for smaller transactions. It also makes for breathing space for the RDBMS, at times boosting operation speed, and at times prolonging operation speed at will.

In this post I show how split exposes itself to the user, should the user wish so.

split can

I'm very happy to announce the release of common_schema, version 1.1 (revision 300).

This version boasts with compelling new features: innovative QueryScript syntax, libraries, views which add to your skills as a DBA, making some maintenance and management tasks a breeze.

• QueryScript, split statement: automagically break long queries into smaller chunks, avoid long locks and reduce query/transaction overhead
• QueryScript, try-catch statement: just try { something; } catch { act_on_error; }.
• killall(): quickly kill connections based on grantee/user/host information.
• profiling/profiling_last: utility views to assist in query

Have just read INSERT, Don’t DELETE by Aaron Brown, and have some lengthy response, which is why I write this post instead of commenting on said post.

I wish to offer my counter thought and suggest that DELETEs are probably the better choice.

Aaron suggests that, when one wishes to purge rows from some table, a trick can be used: instead of DELETEing unwanted rows, one can INSERT "good" rows into a new table, then switch over with RENAME (but please read referenced post for complete details).

I respectfully disagree on several points discussed.

Lockdown

The fact one needs to block writes during the time of creation of new table is problematic: you need to essentially turn off parts of your application. The posts suggests one could

I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!

I'm dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.

Still hush hush, the development of that feature touched at the very same issues described in Michael's post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a

Some administrative tasks can be simplified by using common_schema/QueryScript. I'm collecting a bunch of these for documentation. Here's one for example:

The DBA/developer has the task of retaining only top 3 most populated countries per continent. That is, she has to DELETE 4th, 5th, 6th, ... most populated counties in each continent.

Is it possible to work out with a single query? Yes. But the query is not pretty. In fact, it is quite complicated, and either involves unintuitive subqueries, or unintuitive hacks. A normal DBA would not want to write, neither maintain this kind of query, unless top-notch-geek, which

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

• QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
• Throttling for queries is now made available via the throttle() function.
• Enhancements to processlist-related views, including the new slave_hosts view.
• Inline documentation/help is available via the help() routine.
• more...

QueryScript

common_schema makes for a QueryScript implementation for MySQL. You can run server side

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
{
  throttle 2;
}

Convert all InnoDB tables in the 'sakila' database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
{
  if ($engine = 'InnoDB')
    ALTER TABLE