MySQL 5.0 has a ton of new features. In fact, several tons. Many people and companies were waiting endlessly for some of these features. Some of the new features include: Views, Stored Procedures and Stored Functions, Triggers, and some extra optimizations. Hoorah.
However, not all is peaches. In fact, there are few peaches to be found. Consider the rest of this post a bitch/complaint session and a call for a return to sanity. Let’s take a look at some of the major features of MySQL 5.0:
Views — Views are a great idea. Overall, they even work great. Their biggest downfall is that their MERGE algorithm doesn’t work with UNION, which means that the view engine falls back to the TEMPTABLE algorithm instead. That means it completely forms the view: running the full UNION, dumping the results into a temporary table, and then applying the view against that temporary table. What that really means is that views are useless for replacing MyISAM merge tables, which means we still have no way to do the same thing with InnoDB.
Stored Procedures — No way to trigger or return an error, since no form of RAISE is implemented. Yes, I know you can do other hacks to make it error, but that’s useless.
Stored Functions — I think they might have gotten stored functions right. They aren’t terribly complicated, though.
Triggers — Okay, I don’t even know where to start on triggers. They have a number of major problems:
- Only one trigger per table per action — MySQL missed the point of one of the main applications of triggers: auditing. Since you can only have one trigger per table per action, you cannot use triggers for the typical application purposes at the same time as you use it for auditing. Yes, a single trigger can take more than one action, but that’s not the same thing.
- Replication — Replication of triggers is still fundamentally broken in 5.0.19. You can, on the master, create a trigger that breaks replication. Yes, it’s fixed in 5.0.20, but that doesn’t help me, now does it?
- Upgrading — An upgrade from 5.0.16 to 5.0.19 turned ugly because of some trigger compatibility issues.
- Inconsistent syntax — There is no DROP TRIGGER IF EXISTS for some reason.
- No atomic replacement — MySQL doesn’t support the OR REPLACE syntax to CREATE TRIGGER meaning that you cannot atomically replace (update the definition of) a trigger on a running installation. Strike two for using triggers for auditing.
Memory Leaks — Okay, this isn’t actually a feature, but a bug. A server running 5.0.19, using all of the above features except for views, is leaking memory like crazy. I’ll write a new post once I track it down.
How are you all dealing with MySQL 5.0? Are you as disappointed as I am?
UPDATE: Looks like stored procedures with replication is broken, too. :(