Skip to content

# Tag: openark kit

## mk-schema-change? Check out ideas from oak-online-alter-table

MySQL openark kit Schema scripts March 10, 2010

In response to Mark Callaghan’s post mk-schema-change.

I apologize for not commenting on the post itself, I do not hold a Facebook account. Anyway this is a long write, so it may as well deserve a post of its own.

Some of the work Mark is describing already exists under openark kit‘s oak-online-alter-table. Allow me to explain what I have gained there, and how the issue can be further pursued. There is relevance to Mark’s suggestion.

oak-online-alter-table uses a combination of locks, chunks and triggers to achieve an almost non-blocking ALTER TABLE effect. I had a very short opportunity to speak with Mark on last year’s conference, in between bites. Mark stated that anything involving triggers was irrelevant in his case.

The triggers are a pain, but I believe a few other insights from oak-online-alter-table can be of interest. Continue reading » “mk-schema-change? Check out ideas from oak-online-alter-table”

## RPM builds for openark kit

MySQL openark kit December 10, 2009

Thanks to Lenz Grimmer, openark kit is now available in RPM format.

.DEB or python packages are available, as usual, on project page on Google Code.

Thank you, Lenz!

## New and Noteworthy in openark kit

MySQL openark kit June 30, 2009June 30, 2009

A new release of openark kit is out. Some interesting additions.changes are:

- oak-online-alter-table: now works with multicolumn UNIQUE KEYs
- New: oak-chunk-update: break an long running or non indexed UPDATE/DELETE to small chunks
- oak-purge-master-logs, oak-show-replication-status: support for non standard slave ports

openark kit is a set of utilities for MySQL, helping in easing out everyday’s work. Let’s look more closely at the changes.

#### oak-online-alter-table

The utility allows for non-blocking ALTER TABLE operations, under certain limitations. One limitation which has been removed in the current release was the single-column UNIQUE KEY limitation. As of now, a requirement for running oak-online-alter-table is that the altered table has some UNIQUE KEY. It could be numerical, textual, single column, multi-column (compound), anything. Continue reading » “New and Noteworthy in openark kit”

## Online ALTER TABLE now available in openark kit

MySQL openark kit March 24, 2009June 1, 2011

A new utility in openark kit allows for online ALTER TABLE operation. That is, the modification of table structure without locking down the entire table for the duration of the operation. The oak-online-alter-table utility works under the following restrictions:

- The table has at least one single-column UNIQUE KEY [*]
- Altered table shares a single-column UNIQUE KEY with the original table [*]
- No ‘AFTER’ triggers are defined on the table (the utility creates its own triggers for the duration of the operation)
- The table has no FOREIGN KEYs [*][#]
- Table name is no longer than 57 characters

[*]: Restriction is scheduled to be removed or partly removed.

[#]: ‘Child-side’ foreign keys may actually work, but have not been tested.

Follows is a mini FAQ which attempts to introduce the utility.

#### So what exactly does this utility provide?

- First and foremost, the ability to perform a non blocking ALTER TABLE. This has long been an issue with MySQL, and complex Master-Master, application aware solutions are currently required in order to perform an ALTER TABLE with minimal downtime. The utility offers a no-downtime solution, albeit there is performance penalty for the duration of its runtime, and some requirements to meet.
- It also supports a ‘null’ ALTER. That is, an ALTER TABLE which does not change anything. This effectively means rebuilding of the table. For InnoDB tables with innodb_file_per_table, for example, this could be the means of regaining disk space after removing many rows from the table. Also, while it does not strictly act like OPTIMIZE TABLE, the effect of running this utility should build a better organized table on disk (this as yet unverified).
- Another thing this utility supports is the building of a ghost table: a duplicate of a given table, which keeps mirroring the original table via triggers. [May be removed in future versions]

#### Continue reading » “Online ALTER TABLE now available in openark kit”

## Blocking user accounts

MySQL openark kit Security March 5, 2009February 8, 2011

A long time missing feature in MySQL is temporarily blocking accounts: denying a user to log in, without affecting any other of her privileges.

There is no such privilege as ‘LOGIN’ in the grants table, as the ability to log in is the most basic one MySQL allows. This basic privilege is called USAGE.

I’ll present a hack around this, one which oak-block-account implements. Before presenting the hack, lets lay down some requirements:

- A user can be blocked from logging in to MySQL.
- Such a blocked user can later be ‘released’, re-enabling him to log in.
- It should be possible to determine if a certain user is currently blocked or not.

Continue reading » “Blocking user accounts”

## Announcing openark kit

MySQL openark kit python February 25, 2009March 13, 2009

It is my great pleasure to announce the availability of the openark kit, a set of lightweight utilities for MySQL, which eases every day tasks.

The available tools are:

- oak-apply-ri: apply referential integrity on two columns with parent-child relationship.
- oak-block-account: block or release MySQL users accounts, disabling them or enabling them to login.
- oak-kill-slow-queries: terminate long running queries.
- oak-modify-charset: change the character set (and collation) of a textual column.
- oak-purge-master-logs: purge master logs, depending on the state of replicating slaves.
- oak-security-audit: audit accounts, passwords, privileges and other security settings.
- oak-show-limits: show AUTO_INCREMENT “free space”.
- oak-show-replication-status: show how far behind are replicating slaves on a given master.

Continue reading » “Announcing openark kit”