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 →

Just for the pure insane fun of it, I accepted the challenge of “what can you do with the text format of the schedule?” for BarCampMel. I’m a database guy, so I wanted to load it into a database (which would be Drizzle), and I wanted it to be easy to keep it up to date (this is an unconference after all).

So… the text file itself isn’t in any standard format, so I’d have to parse it. I’m lazy and didn’t want to leave the comfort of the database. Luckily, inside Drizzle, we have a js plugin that lets you execute arbitrary JavaScript. Parsing solved. I needed to get the program and luckily we have the http_functions plugin that uses libcurl to allow us to perform HTTP GET requests. I also wanted it in a table so I could query it when not online, so I needed to load the data. Luckily, in Drizzle we have the built in EXECUTE functionality, so I could just use the JavaScript to parse the response from the HTTP GET request and …

When you're spoiled with Oracle's fabulous query transformation capabilities and its really well-done cost-based optimiser, then you might forget how difficult SQL query tuning used to be in the "old days" or with those less sophisticated databases. Here's a really nice explanation of the various means of implementing an ANTI-JOIN in MySQL: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

I have seen a few posts on DBA.SE (where I answer a lot of questions) recommending the use of semi-synchronous replication in MySQL 5.5 over a WAN as a way to improve the reliability of replication. My gut reaction was that this is a very bad idea with even a tiny write load, but I wanted to test it out to confirm. Please note that I do not mean to disparage the author of those posts, a user whom I have great respect for.

What is semi-synchronous replication?

The short version is that one slave has to acknowledge receipt of the binary log event before the query returns. The slave doesn’t have to execute it before returning control so it’s still an asynchronous commit. …