MySQL 5.1 added a long-needed feature: INFORMATION_SCHEMA tables for inspecting locks held and waited-for inside of InnoDB. With this addition, it became possible to figure out who is blocking whom.

MySQL 5.5 changed a lot more things inside the server. One of the new features is improved metadata locking. This solves a lot of inconsistencies and bugs that were previously possible. The output of SHOW PROCESSLIST is also changed; instead of the venerable Locked status, there are more fine-grained status indicators such as Waiting for table metadata lock.

Unfortunately, the additional locking is not possible for the DBA to inspect. Where InnoDB’s storage-engine-level locking used to be …

Although not perfect, MySQL replication was probably the killer feature that made MySQL the default database for web applications some time ago. Since then, MySQL replication has been improved greatly, with such notable changes as row-based replication. At the same time, the replication engineering team has made MySQL replication more conservative and less forgiving of foot-gun errors. These have gone a long way towards helping users avoid some of the problems that made replication sometimes drift out of sync with the master copy, sometimes silently.

In some cases I think the strictness has gone a little too far. One example is the server’s identification of statements that are unsafe for replication because they are nondeterministic. Here is a statement in an application I manage, which is designed to claim some work from a queue. After running this statement, the application checks if any rows were affected, and if so, it then fetches …

Unfortunately, MySQL 5.5 doesn’t generate a very good query execution plan for IN() queries against a compound (multi-column) index, such as the following query that should be able to use the 2-column primary key:

explain select * from tbl1
where (col1, col2) in (
      (732727758,102),(732728118,102),(732728298,102),(732728478,102),
      (732735678,102),(962074728,102),(964153098,102),(2027956818,102),
      (2034233178,102),(2034233538,102))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1379
        Extra: Using where

Queries such as this should usually be rewritten to a form such as the following, which accesses only the 10 rows specified instead of scanning the table:

explain select * from tbl1
where (col1=732727758 and …

I decided to take another look at MySQL 5.6, and realized that I’ve forgotten how many changes this version will have. Each milestone has many improvements, and there have been many milestones, so my memory of the older ones grows hazy.

The Fine Manual has the details, but here is my attempt at a quick (and probably incomplete and inaccurate) summary. I’ve emphasized a few changes that will make life significantly better for me.

• InnoDB: supports fulltext search; more kinds of ALTER TABLE avoid copying/rebuilding the table, some without blocking the table at all (truly online ALTER TABLE); more flexibility with data files; improvements to compression; improvements to flushing to avoid checkpointing stalls; ability to access InnoDB tables through the memcached protocol instead of SQL; more INFORMATION_SCHEMA tables; persistent …

For a long time I’ve been maintaining a set of scripts inspired by Giuseppe Maxia’s MySQL Sandbox, which is a Swiss Army Knife for starting and stopping server instances for jobs such as testing, development, trying out a new version, and so on. My scripts are unpublished, until now. I’ve just kept them in my Dropbox’s bin folder, which I add to my $PATH.

It’s not worth explaining why I use my own scripts, except for saying that I keep dozens or even more MySQL versions unpacked in my home directory at any given time, and I find it a little easier to use these lightweight scripts than the more fully-featured MySQL Sandbox tools.

Usage assumes some conventions are followed. I “install” each version of the server by downloading the generic tarball. Then I unpack it and move it to $HOME/mysql/servers/VERSION, where VERSION is something like 5.5.27. If it …

Someone recently asked me why the wrong index was being used for a JOIN, making the query run very slowly. We ran EXPLAIN and saw this abridged output:

explain select [columns] from m
   left join u on m.intcol = u.intcol and m.url = u.url
where u.url is null\G
*************************** 1. row ***************************
  select_type: SIMPLE
        table: m
         type: ALL
         rows: 2717
*************************** 2. row ***************************
  select_type: SIMPLE
        table: u
         type: ref
          key: idx_intcol
      key_len: 2
          ref: m.intcol
         rows: 64486
        Extra: Using where; Not exists

The column is a 2-byte unsigned integer. Here is the relevant part of the table definition:

CREATE TABLE `u` (
  `intcol` smallint(5) unsigned NOT NULL,
  `url` varchar(760) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  UNIQUE KEY `url` (`url`,`intcol`),
  KEY …

Mail’s Mail.app keeps a list of recent recipients, but it doesn’t let you export them to a file. You could do as some others on the Internet have suggested and grep the file of addresses for everything that looks like an email address, or you could work with your system instead of against it!

The trick is twofold: knowing where the addresses are stored, and knowing how. They’re stored in a file in your user directory, Library/Application Support/AddressBook/MailRecents-v4.abcdmr.

The “how” is the fun part. It’s an SQLite database file. Now all you have to do is open the file with SQLite and select the data from it! The full power of SQL is at your disposal. Here’s a sample:

$ sqlite3 ~/Library/Application\ Support/AddressBook/MailRecents-v4.abcdmr

Here is an SQL command you can enter to select all of the emails, with first and last names:

select '"' || ZFIRSTNAME …

I’m using innotop again every day, for the first time in a few years. I found that I didn’t like the tool that the younger and less experienced version of me created. It is very flexible and has the ability to surface a lot of information about MySQL, but not all on one screen. I wanted a “single pane of glass” health dashboard for the servers I’m monitoring, instead of having to look on various screens for important bits of information.

The good news is, innotop is very extensible and I know the code because I wrote most of it, so in a short while I had a dashboard that suited me. I committed these changes to trunk, so if you wish you can easily get the code:

$ wget innotop.googlecode.com/svn/trunk/innotop
$ chmod +x innotop
$ ./innotop

You can select the new ‘A’ mode by pressing the capital ‘A’ key. Here is a screenshot (click for full size version):

…

Killing long-running idle transactions is a good way to increase the uptime of a MySQL server. This may sound strange, but open transactions will eventually bring the server down, and it is better to hurt a single application than the many that will be hurt when that happens.

Long-running idle transactions are usually caused by a programmer mistake or an unexpected condition that causes an application not to be able to do its work. The potential number of sources for such problems is unlimited, so it’s virtually impossible to prevent long-running transactions. You can find and solve them when they happen, but you can’t ensure that you’ll never get one from an unexpected source (because, by definition, the source is unexpected).

That is why it’s a good idea to run an idle-transaction killer. There are also other types of things you can profitably kill and help your uptime even more, but those are sometimes more complex to …

MySQL is a database of compromise. Compromise between running a production-ready relational database and being popular with all sorts of hackers - mostly the ones that don't really like SQL. And because they don't really like SQL, they choose MySQL, as MySQL is very forgiving. It is just as forgiving as their favourite language PHP, … Continue reading MySQL Bad Idea #384 →