LOCK TABLES in MyISAM is NOT a poor man’s tranactions substitute

MySQL

I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us ‘transactional flavor’.

It isn’t, and here’s why.

When we speak of a transactional database/engine, we check out its ACID compliance. Let’s break out the ACID and see what LOCK TABLES provides us with:

  • A: Atomicity. MyISAM does not provide atomicity.  If we have LOCK TABLES followed by two statements, then closed by UNLOCK TABLES, then it follows that a crash between the two statements will have the first one applied, the second one not not applied. No mechanism ensures an “all or nothing” behavior.
  • C: Consistency. An error in a statement would roll back the entire transaction in a transactional database. This won’t work on MyISAM: every statement is “committed” immediately.
  • I: Isolation. Without LCOK TABLES, working with MyISAM resembles using the read uncommitted, or dirty read isolation level. With LOCK TABLES – it depends. If you were to use LOCK TABLES … WRITE on all tables in all statements, you would get the serializable isolation level. Actually it would be more than serializable. It would be truely serial.
  • D: Durability. Did the INSERT succeed? And did the power went down just after? MyISAM provides not guarantees that the data will be there.

Continue reading » “LOCK TABLES in MyISAM is NOT a poor man’s tranactions substitute”

MySQL User Group Meetings in Israel

MySQL

This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period).

Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we’ve had 2 successful meetings, and we intend to have more! First one was in Sun’s offices in Herzlia; second one, held last week, was at Interbit (a MySQL training center) in Ramat Gan. We hope to hold these meetings on a monthly basis, and the next ones are expected to be held at Interbit.

A new (blessed) law in Israel forbids us from sending invitations for these meetings via email without prior consent of the recepient (this law has passed as means of stopping spam). We do realize there are many users out there who would be interested in these meeting. For those users: please stay tuned to Interbit’s website, where future meetings will be published – or just give them a call!

It was my honor to present a short session, one of three in this last meeting. Other presenters were Erad Deutch, who presented “MySQL Success Stories”, and Moshe Kaplan, who presented “Sharding Solutions”. I have presented “MyISAM & InnoDB Tuning Fundamentals”, where I have layed down the basics behind parameter tuning for these storage engines.

As per audience request, here’s the presentation in PDF format:

I intend to give sessions in future meetings, and have already started working on my next one. So please come, it’s a fun way to pass a nice afternoon. See you there!

Blocking user accounts

MySQL

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

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

MySQL

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

MySQL

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”

MySQL security: data integrity issues

MySQL

MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.

I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.

This post is not about Roles, Kerberos, IPs and such. It’s about simple MySQL features, which allow common, unprivileged users, to break data integrity by using unprotected session variables.

I will consider three such issues.

Continue reading » “MySQL security: data integrity issues”

Triggers Use Case Compilation, Part II

MySQL

In Triggers Use Case Compilation, Part I, I’ve demonstrated some triggers use scenarios.

We continue our examples of triggers usage.

Counters and aggregations bookkeeping

Consider the City table: each city belongs to a certain country. Some questions we may be interested in are:

  • How many cities are there per country?
  • What’s the sum of cities population per country?
  • What’s the population of the largest city per country?

Answering any of these questions is an easy SQL excercise. But aggregation is required, and full table scan (or full index scan, if we’re lucky) is essentially part of any execution plan. What if we can’t pay the price for these queries? What if we need immediate, or near immediate response?

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