MySQL has gotten better as time has gone on. I want to make that clear up front before I bash a handful of things about its current state. It has gone from being little more than an SQL front end to flat files to being almost a real database (if you use InnoDB and friends).
- No full outer join; this one irritates me to no end when working on diff-type queries (ones that, like the diff utility in UNIX, take a set of rows and compare them against another set, getting a difference) because I have to union three queries together, rather than simply one query with a full outer join.
- Constraints and concurrency are not enforced by default. You have to setup InnoDB to make it work properly. There is simply no excuse for not maintaining relationships in a RELATIONAL DATABASE. InnoDB is great, don't get me wrong, but I should not have to setup an add-on (and to run properly, you will need to configure the engine, at least a little) to get something so basic and fundamental. With MySQL 6.0, Sun has promised an end to this with Falcon, but that has yet to happen. 6.0 isn't out yet, and it wouldn't be fit for production use for a while longer even if it were.
- Stored procedures. These were not added until version 5.0 of MySQL (many shops and shared hosts are still on 4!), but now they are here--sort of. The fact of the matter is that stored procedures really aren't usable in MySQL. The syntax is clumsy, requiring messing with delimiters to even create them. They do not work from the command line because of this, which makes testing harder. In addition, the syntax is lacking quite a bit featurewise. The easiest example of this is also what should be simplest: how do you iterate over a cursor? Simple 101 feature, right? Not really. At least, not in MySQL.
Those are the biggest things I can think of off the top of my head. I've got a hunch that I would not be happy about replication or binary logging either if I had the time to set them up. Now for any of you reading this (if anyone does read this), you may ask: why not just use PostgreSQL? Or Oracle? Or even Microsoft SQL Server? The shop I am working in will not invest the sums for Oracle or MS SQL Server, so those are out. The current reality is that we will not be leaving MySQL any time soon.