The world’s most popular database is also the VHS of databases. It gets the job done, but the picture is grainy, the sound wows in and out, and it gets jammed once in a while. That’s what I found out on a recent project with MySQL 4.1.
First, some context.
I recently launched a dating website for Luxembourg. “Oh no!” you scream, “Not another bleedin’ dating website!”. No apologies though; Luxembourg needs some loving, and I was the man for the job.
As part of my preparations I wrote a few libraries to flex my Python muscles, which is to say that I didn’t want to write any namby-pamby front-end code just yet. I’m a real hacker goddammit and I’m going to build me some libs.
So I wrote a templating system, some tools to wrap around Apache/mod_python, and collected several other little things into a common package.
I also needed an object-relational mapper, or ORM if you’re short of time and/or breath. I kept cutting my fingers on the sharp edges of SQLObject, and the sheer volume of impenetrable documentation for SQLAlchemy was intimidating, so I wrote my own. In fairness, the documentation for SQLAlchemy was probably fine, and SQLObject could have been fixed, but I wanted to do it my way, being a real hacker and all.
And thus began the journey which led me to conclude that I’m not going to use MySQL any more.
I’m not going to actually relive that journey with you. It was long and had lots of swearing. My wife can let you know all about the frantic desk-slamming, the irritated pacing, and the endless cups of tea. I did a lot of cursing during my skirmishes with mod_python too, and my choice of (human) language was generic/base enough that my wife probably could not discern the MySQL-induced tantrums from the mod_python ones.
I didn’t pull my hair out though. That’s because I hardly have any, and standing in front of a mirror trying to find strands to tear out with tweezers gets in the way of the ‘moment’.
Back to the point, my beefs with MySQL.
Doctor, …
CTRL-C does not do what it says on the tin
Firstly, and this is a bit petty, but when I type CTRL-C in the mysql command-line client, the client exits. This is really annoying, because I have learned that CTRL-C in every other interactive (*nix) command-line means either (a) ditch the current line, or (b) cancel the current command. There is no “(c) Exit”, because it doesn’t exist. Except for MySQL, where you are instantly dumped back to your shell, transactions turned to dust, and left with that bubbling sensation at the top of your head as the blood boils and takes another couple of months off your life.
REFERENCES
Declaring a column with a REFERENCES clause has no effect. In the words of a friend, this makes the baby Jesus cry.
You may ask “Why is this a problem?”, and you’d be right to ask. “It’s there for compatibility with other dialects of SQL” is a good answer, but not good enough. “It’s a feature that will be introduced later on” is another reply, to which I laugh heartily, then shed a small tear of my own. The problem is that it is misleading and creates a false sense of security. People believe that they have an extra tool to maintain the consistency of their data, but alas, it is not so. MySQL is a database, and as such it should not go playing fast and loose with the data that it holds.
This is another example of unexpected results, but more fundamental than the CTRL-C thing. I know it’s in the docs, but my brain is not big enough to remember every gotcha in the book. Someone else said this first, but software should either work, or fail hard. MySQL should not accept my request; it should totally barf on my shoes. Perhaps there should be an option to ignore these clauses, but that should be the thing that you have to look in the manual to discover, and the default should be to either respect them or throw them back in your face.
Foreign keys
I first learnt about relational databases on Oracle 7, so I quickly learnt to appreciate foreign keys and all other kinds of constraints. I could not get enough of the lovely little hairy fellows. Of course, there is a limit to how well they can keep your data in pristine condition, but they do help to catch problems early, especially during development.
So I was glad to see that MySQL supports foreign keys on InnoDB tables, and have eagerly used them. But they have shortcomings.
Like, not actually working.
I restored some invalid data from a dump, just for fun (actually, I was being stupid, but to admit that would make me appear fallible, which, as we both know, I am not). It was invalid because it should have violated at least one foreign key constraint, but MySQL didn’t notice. At first I thought that MySQL had probably disabled constraints during loading, then had problems re-enabling them at the end, and not told me about it.
But the keys were enabled, and I was unable to add new invalid data to the table. I deleted some of the invalid data and tried to re-insert it, but that was denied too. Hmm.
Data consistency is bread-and-butter stuff for a relational database, and I subverted it unexpectedly. If a constraint is enabled, I fully expect that nothing violates that constraint, and it’s not unfair to insist on that.
Foreign keys in MySQL are still useful, but they are no guarantee that the data is consistent. And to compensate, we have to write some scripts to verify our data, one of the things that we hoped to avoid by using foreign keys.
Okay, fair enough, this is probably a bug. At least, I hope it’s not a feature. But this is core functionality, and a gaping hole like this makes me tremble like a chicken at a KFC.
Default values
mysql> CREATE TABLE a (
-> b VARCHAR(10) NOT NULL,
-> c VARCHAR(10) NOT NULL,
-> d INT NOT NULL
-> );
mysql> INSERT INTO a (b) VALUES ('b');
mysql> SELECT * FROM a;
+---+---+---+
| b | c | d |
+---+---+---+
| b | | 0 |
+---+---+---+
1 row in set (0.00 sec)
Whoops, I forgot to set values for the c and d columns. But how cute, MySQL has filled them in for me. That’s why it’s called MySQL, because it always looks out for me, aah.
NO! I set them NOT NULL with no default so that I would be forced to enter a value. If I don’t enter a default, don’t guess one for me. Tell me I’m an idiot and get me to think again. Why is there an assumption that the empty string or zero is good for me, now that I’ve rejected NULL?
“I don’t want to work for NULL euros an hour!”
“Okay, let’s put you on our ZERO euros an hour rate instead.”
Roll it all up
You’ve probably seen the pattern. Most or all of my problems with MySQL comes from differences between what I expect will happen, and what actually does happen. But I know these quirks now so they won’t cost me much development time any more, and I’ve built my ORM to cover the most obvious cracks.
But something still bothers me. The water still isn’t clear and I’m afraid there’s a slimy green monster in the depths. What quirks do I not know? Will they one day bite me on the rear like a three-jawed radioactive toad on an arse biting frenzy?
The default MyISAM engine lacks foreign keys and transactions, but InnoDB feels tacked on, and foreign keys still don’t work properly. MySQL tries to please everyone by accepting, yet ignoring, things like REFERENCES clauses. When inserting it has a habit of choosing default values for NOT NULL columns instead of telling me I’ve forgotten to specify a value. In trying to be accommodating it actually ends up surprising and frustrating.
Bizarrely enough, I still like MySQL. In the end it’s worked well for me. The performance is good, and it is easy to maintain and backup. But I won’t be using it again. When storing mission-critical data it is vitally important to do the right thing, and MySQL often does not do the right thing.