[prev] [thread] [next] [lurker] [Date index for 2006/10/19]
(This started out as a polite response on another mailing list to someone
saying "You guys are out of date, MySQL is a pretty decent db now". But it
got a bit ranty, so I thought I'd share it with all you software haters
instead. Enjoy!)
Current releases of MySQL certainly have many of the checklist features of a
real DBMS. However, this does not constitute a defensible claim that MySQL
is a real DBMS.
MySQL still, after all these years, has a whole host of arbitrary
limitations (like the limits on key length, and the inability to use TEXT
columns in a foreign key, and the lack of recursion in stored functions).
And there are still important features missing, whatever table engine you
use (like CHECK constraints, and deferred constraint checking).
Worst of all, though, MySQL seems to deliberately make it hard to use the
features it does have. A recent project needed transactions and foreign-key
references in a MySQL 5.0 database (the current stable release, please
note), so we used InnoDB tables. The situation was broadly equivalent to
this:
CREATE TABLE actor (
id INT PRIMARY KEY AUTO_INCREMENT,
name TEXT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE film (
id INT PRIMARY KEY AUTO_INCREMENT,
name TEXT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE film_actor (
film_id INT NOT NULL REFERENCES film,
actor_id INT NOT NULL REFERENCES actor,
PRIMARY KEY (film_id, actor_id)
) ENGINE=InnoDB;
Simple enough, yes?
No, actually. As it happens, using REFERENCES as a column constraint
doesn't work at all, and you also have to explicitly specify the primary-key
columns of the referenced table. So the only permitted syntax for foreign
keys is this (which you'll notice is much more verbose than the obvious
version):
CREATE TABLE film_actor (
film_id INT NOT NULL,
actor_id INT NOT NULL,
PRIMARY KEY (film_id, actor_id),
FOREIGN KEY (film_id) REFERENCES film (id),
FOREIGN KEY (actor_id) REFERENCES actor (id)
) ENGINE=InnoDB;
So guess what happens when you use one of the naughty-but-convenient
syntaxes?
That's right -- MySQL just silently ignores what you said! No error, no
warning, just a blithe "sure, no problem mate, I'll get right on it",
without actually doing what you asked.
Why, yes, this _did_ cost me a day of debugging, thanks for asking.
I'm aware of MySQL's propensity for handling compatibility requirements by
"helpfully" ignoring any bits of syntax that the engine doesn't have the
semantics for. But what made me so fucking angry about this was that the
engine _does_ have the semantics for this! How hard could it be to just
do the right thing?
Hate.
--
Aaron Crane
Generated at 16:01 on 02 Nov 2006 by mariachi 0.52