Announcing openark kit

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:

Continue reading » “Announcing openark kit”

Manually installing multiple MySQL instances on Linux: HOWTO

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”

Triggers Use Case Compilation, Part III

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”