February 2009 – code.openark.org

Announcing openark kit

MySQLopenark kit pythonFebruary 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”

Manually installing multiple MySQL instances on Linux: HOWTO

MySQLConfiguration Installation LinuxFebruary 19, 2009September 17, 2010

Installing a single MySQL instance on a linux machine is a very simple operation. It may be as simple as:

apt-get install mysql-server

But you cannot use this method to install another MySQL instance. Moreover, if you try to manually install another instance, you may find that some collisions occur.

For example, when trying to install two 5.0 servers, apt-get or yum will just tell me “package is already installed”.

When trying to install a new 5.1 server along with 5.0, an implicit upgrade is performed.

But even if we try forcing the installation, there are collisions:

A dpkg or rpm will install my.cnf under /etc. Two installations will override one another. With RPM you may get a .rpmsave backup file, but that doesn’t help too much.
The daemon file: /etc/init.d/mysql(d) is overwritten.
The default data directory is used for both installations: /var/lib/mysql
The binaries are overwritten
Both installations will use port 3306.
In both installations, the same socket file (e.g. /var/run/mysql/mysql.sock) is used.

Continue reading » “Manually installing multiple MySQL instances on Linux: HOWTO”

`;`.`*`.`.` is a valid column name

MySQLSecurity SyntaxFebruary 12, 2009February 12, 2009

And the following query:

SELECT `;`.`*`.`.` FROM `;`.`*`;

is valid as well. So are the following:

DROP DATABASE IF EXISTS `;`;
CREATE DATABASE `;`;
CREATE TABLE `;`.`*` (`.` INT);
CREATE TABLE `;`.```` (`.` INT);
CREATE TABLE `;`.`$(ls)` (`.` INT);

Continue reading » “`;`.`*`.`.` is a valid column name”

Triggers Use Case Compilation, Part III

MySQLEvent Scheduler TriggersFebruary 2, 2009February 2, 2009

The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations.

Triggers Use Case Compilation, Part I

Triggers Use Case Compilation, Part II

Limitations and wishful features

Triggers are slow

The overhead of adding triggers is usually an even breaker. But I would like to believe speed will improve in time!

Triggers cannot act on the same table which activated them.

A thing I would like to do is have a rotating table. A log table is a perfect example: I only want to store logs up to 7 days back, or up to 1M rows. ON INSERT, (or once every 1000 inserts or so), I wish to remove oldest rows. This is not possible today since I can’t DELETE rows from the same table which caused the ON INSERT trigger to run. It can’t be hacked by calling on another table, then doing a circular trigger trick. MySQL will raise an error on run time, complaining about a loop.

Continue reading » “Triggers Use Case Compilation, Part III”