I’ve been following the Drizzle project with some interest. There’s a lot to like about it. But you know what I like most about the project?

No dual licensing. Just plain GPL, version 2.

I personally think this is the foundation for why people are empowered, why there is excitement, why there is progress, [...]

Rob Young has posted a few blog entries lately on the MySQL Enterprise monitoring software. His latest post claims that agent-based monitoring is equivalent to extensibility (MySQL Enterprise Monitor: Agent = Extensibility).

I think this is conflating two completely distinct properties of a monitoring solution. Cacti is extremely extensible, with a plugin-based architecture and [...]

I got some interesting comments on my previous article about unit testing Maatkit, including echoes of my own conversion to the unit-testing religion. One of the objections I’ve heard a lot about unit-testing is how it’s impossible to test code that talks to a database. “It’s too hard,” they say. “Oh, it’s easy to test a module that calculates a square root, but a database? Way too much work!”

Note: As commenters have pointed out, I’m not necessarily using “unit” in the agreed-upon way here. Everything I say can be applied to ultra-pure unit testing too, but I go beyond that. I will hold fast to my assertions about mocking though *grin*

Is it really impossible or even hard?

I disagree. In one of my previous articles I said …

Over in Maatkit-land, Daniel Nichter and I practice test-first programming, AKA test-driven development. That is, we write tests for each new feature or to catch regressions on each bug we fix. And — this is crucial — we write the tests before we write the code.* The tests should initially fail, which is a validation that the new code actually works and the tests actually verify this. If we don’t first write a failing testcase, then our code lacks a very important guarantee: “if you break this code, then the test case will tell you so.” (A test that doesn’t fail when the code fails isn’t worth writing.)

Most of the time when I do this, I write a test, it fails because I haven’t written any code yet, and I then go do some kind of …

Want to know the type of an arbitrary expression in MySQL? Someday in the far far future in version 7.1, you might be able to with the TYPEOF() function.

For now you can try this:

CREATE TEMPORARY TABLE typeof AS SELECT [expression] AS col;

For example, let’s see what the type of CRC32 is.

mysql> CREATE TEMPORARY TABLE typeof AS SELECT CRC32('hello world') AS col;
mysql> DESCRIBE typeof;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| col   | int(10) unsigned | NO   |     | 0       |       | 
+-------+------------------+------+-----+---------+-------+

This is one possible way to programmatically determine the type of an expression — even an arbitrarily complex one.

Not beautiful, but …

Dear LazyWeb, I want to use my Ubuntu laptop (on amd64 BTW) to build an RPM of Maatkit that will work on all RPM-based distros. Is it possible? Or are there enough differences between the RPM-based distros that I can’t do it? Mind you, the finished RPM ought to just have some man pages and Perl scripts, so I don’t think it will be platform- or distro-specific. But I am just not an expert on it.

The second question is, what do I need to put into my Makefile to do this? My ‘make all’ currently builds a .zip, a .tar.gz, and a .deb package — what needs to change to make that include .rpm?

Someone who is willing to help create .spec files, etc, etc will be immediately given commit rights to Maatkit’s SVN repository!

Debian, packaging, …

Download Maatkit

Maatkit version 2152 is ready for download. This release is also known as the “is this project really alive?” release. We thought we should delay until MySQL released a new Community Server version. Just kidding — it has nothing to do with that.

This release is also very significant in that it’s the first one that has large code contributions by someone other than myself. As you may know, Percona (my employer) has hired the very talented Daniel Nichter, author of mysqlreport and other goodies, to help with Maatkit. So far it is a match made in heaven, and Daniel did …

This is a continuation of my articles on how to select the desired rows from ranked data. A user recently posed a question in the comments that I thought was particularly intriguing:

What is the best way to query 1) Sum of min price of all types? 2) Sum of 2nd highest price of all types?

Sounds like fun! Let me start by saying the sum is the easy part. You can always do that like so:

select sum(price) from (
   -- find desired rows here
) as x;

Finding the desired rows is the hard part. In my previous articles I focused on extrema:

• The single biggest/smallest/extremest row in each group. (Pretty easy.)
• The N most extreme rows in each group. (Doable, but harder.)

In this article, we’re going to see how to get not the most extreme row, not the N most …

I follow only a few comics in my feed reader: Get Fuzzy, Dilbert, XKCD, and now The Adventures of Ace, DBA. It’s kind of XKCD-ish, only it’s about Oracle and it doesn’t have the extra punch line when you hover your mouse over the picture. And it’s proof that a DBMS (even one I don’t use) can be pretty damn funny.

comics, humor, Oracle

This post is SEO bait for people trying to scale MySQL’s write capacity by writing to both servers in master-master replication. The short answer: you can’t do it. It’s impossible.

I keep hearing this line of reasoning: “if I make a MySQL replication ‘cluster’ and move half the writes to machine A and half of them to machine B, I can increase my overall write capacity.” It’s a fallacy. All writes are repeated on both machines: the writes you do on machine A are repeated via replication on machine B, and vice versa. You don’t shield either machine from any of the load.

In addition, doing this introduces a very dangerous side effect: in case of a problem, neither machine has the authoritative data. Neither machine’s data can be trusted, but neither machine’s data can be discarded either. This is a very difficult situation to recover from. Save yourself grief, work, and money. Never write to both …