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. :(
Jeremy Cole 
Hi, I feel your pain and witnessed some of your difficulties while trying to migrate Firebird (Interbase)’s default EMPLOYEE database to MySQL.
Regards and keep us posted
It would be great if you keep us posted. I ran into the same problems with memory leaking when I tried the 5.0.19 version of MySQL.
No way I am upgrading my 4.1 servers. I am not even sure why it’s called production and I’d like to see way more 4.x (or 5.x) versions before a new major release.
All this catching up on features just introduces messyness. MySQL is already a very popular database, no need to win any awards with “new features”. I would like to see more focusing on what MySQL is good. If people crave for other things, let them use another database.
till,
Two suggestions for resolving this problem, in addition to what MySQL itself is doing:
1. Start using the earliest possible alpha or beta versions of a new MySQL release in your test systems as soon as you can. By the time it’s declared production-ready you’ll have found and reported any issues which affect you and MySQL will have fixed them. At which point you have an advantage over your competitors if they have to follow path 2…
2. Wait six months from first production release so even more broad range of users of that can find and MySQL can fix bugs not found during alpha and beta.
All of this applies mostly to those who are risk-averse and want an easy life, or who are operating systems which will make the front page of the Times if they have a big problem.
MySQL itself has a bit of a problem because it has demands from those who need the new features to port their applications so they can start using MySQL. Tough to deny those people the benefits of MySQL. You’ll find more frequent releases with fewer changes to be one of the routes MySQL is taking, so we don’t get the big feature jump from 4.1 to 5.0 and its correspondingly large area for new bugs to lurk.
The best thing you can possibly do is follow path 1. That helps you and every other MySQL user as much as possible.
Joseph Scott’s Blog » Blog Archive » MySQL 5.0: Remarkably Painful
Hi James,
The first production release of MySQL 5.0 was just over 5 months ago. Are you claiming that these major problems will be completely worked out in the next month? I don’t think they will…
In addition, the MySQL downloads page claims:
(emphasis mine)
Jeremy,
Talking here about “make the front page of the Times” cases like Yahoo or Wikipedia or Google.
Six months is generic for the future releases, with fewer change per release. If you’re highly concerned about not encountering any bugs in new features it introduces, not even during your system development and test stages, and have done nothing to help find bugs which affect your systems prior to GA, I’d give 5.0 a full year after GA release or perhaps skip to 5.1 six months after it goes GA. But I tend to be pretty conservative…
That’s not what a company seeking a technological advantage over competitors should be doing, though. Such a company should have been putting 5.0 into test use a year ago and reporting bugs and limitations which affected its systems in development.
It’s OK and completely understandable to complain about bugs after GA and I do it myself but it’s not the most productive course – the most productive one is to test early and often, well before production release.
At a minimum, for your sort of situation, add a slave mysqld per production system and hang it off the rest for a while so you catch any insert/update compatibility issues. Better still, use a complete log of queries and feed production queries to it and report any issues.
I’m assuming here that Yahoo is seeking competitive advantage through technology and could usefully exploit new features in 5.0. If Yahoo is in that situation and it applies to handy things like partitioning and row-level replication, Yahoo should be using 5.1 and 5.2 for development and test today and making MySQL aware of any and all issues it encounters so they can be fixed as soon as possible.
If Yahoo has a support contract with MySQL (remembering that MySQL employees can’t confirm or deny such things without customer permission) it should do so with support issues and should make MySQL aware of its anticipated production deployment timeframes for those systems.
MySQL is a “big” open source developer but the fundamentals of the partnership between developers developing and users finding and reporting bugs still apply just as they do with all open source. The users are a key part of the team. Those who have the resources but aren’t participating early on aren’t acting as full team players.
This team aspect is perhaps too understated with MySQL but it’s very important because MySQL, working with MySQL end users, is trying to push MySQL to the point where almost anyone can reap the benefits of a commodity open source database.
In my personal opinion, Yahoo hasn’t been using its position to help drive the development of MySQL as far and as fast as it could.
Jeremy,
Sometimes user feedback is of the type “Cool! Keep up the great work!”. Other times, users are less appreciative of progress. While the first type of feedback gives warm and fuzzy feelings, the second type is the one that moves us forward. So while I cannot promise any dates for progress, I can tell you that I’m very thankful for your input which helps us focus our efforts in the right direction.
Kaj Arnö
VP Community
MySQL AB
(and incidentally a former boss of Jeremy’s, for any external readers)
James,
You’re missing a key point here: MySQL is selling 5.0 (yes, selling, remember your sales team?) on the web and in person as “done, complete, use it in production”, when that clearly is NOT the case.
One of my key points of this post was not just to “complain about bugs” but to call for a return to some sense of sanity. That is, to face the truth: as of today, 5.0 is NOT, and I repeat NOT “ready for production use”. To claim otherwise, knowing of only the problems above (even though there are others) is disengenuous.
You’re also making an assumption that this post has anything to do with Yahoo!, so I’ll humor you. Yahoo! is currently trying to get caught up to 4.1 in production systems, but we found similar problems with 4.1: subqueries are only half useful, since many common cases are so poorly optimized as to be useless (subquery in IN?); the hostname cache was completely broken until 4.1.13 (when I found and fixed a stupid bug in it); there was (and in MySQL’s releases, still is) no way to get the old timestamp format, making seamless upgrading impossible.
Regards,
Jeremy
Jeremy,
Personally I agree with you and made that view very clear: “not a hope in hell of Wikipedia converting to 5.0 soon” to a range of people at last weeks MySQL developers conference. But that’s more because of 4.0 to 4.1 porting issues than 5.0 bugs.
What I’ve suggested is how end users can help to reduce the problem from that end of things. It’s not the whole picture but it is a critical part of it – it takes end users pointing out problems _early_ in the process to get things changed before something is declared ready for production. If people like you aren’t testing porting and such, it’s not going to be done well enough. I don’t like that, it simply is.
To clarify also, I’m writing here as a Wikipedia person, except where it’s obvious that something is from MySQL. Today Wikipedia uses 5.0 on a single reporting server which is a replication slave to the main servers, which run 4.0.
What else has made porting from 4.0 to 4.1 painful for you?
The other thing that drives me CRAZY is the fact that subqueries don’t use indexes for IN clauses.
For example say you had two tables… FOO and BAR. If FOO has 1M rows and BAR has one then:
SELECT * FROM FOO WHERE BAR_ID IN (SELECT ID FROM BAR)
*** will scan all 1M rows in FOO before returning a match ***
How evil is that?!
Hello,
I’ve been testing 5.0 for 3-4 months and we’ve run into 3-4 very serious issue since it became production. I’ve actually filed couple bugs myself (and they’re released as patches) and to me it seems like bugs were not solved efficiently between minor releases. Take the latest bug Jeremy filed (Stored procedure with singel quote values in it). A similar bug was announced patched with 5.0.19 which addressed some other stored procedure related binary log issues. Now exactly same place, but this time a very simple ‘ escape issue is the problem. I believe MySQL needs to be doing more QA and be extra cautious given that market (us) expects them to be more reliable with 5.0 release. Not to mention Oracle’s last 2 moves already made us anxious to see how MySQL is doing.
Kayra Otaner
Kevin: Oh yes, believe me, I know your pain. That’s one of my other big complaints: subqueries.
Kayra: I think one of the things that is lacking is any real QA testing the interplay between different features. The features are tested individually, and they work fine, but not much effort goes into testing the different combinations of various features.
Specifically, more testing needs to go into making sure that replication doesn’t break in strange ways with every new feature.
Kevin, subquery optimisation has been a pain since introduced in 4.1. Some improvements are planned in 5.2 but that still won’t cover all cases. Best I can suggest is to use the latest practical MySQL version, since they are gradually being improved. They definitely weren’t ready to go except minimally when released in 4.1. The specific poor performance you’re seeing may be explained at http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html which describes how the IN is rewritten by the query optimiser. It’s definitely frustrating that such a common, simple use of subqueries isn’t handled well. It’s still the case that rewriting subqueries as joins is often the best approach.
Jeremy, recently it’s seemed as though there’s been a new, avoidable through basic testing, replication bug in every other release. Very frustrating. It’s an issue which has received serious attention recently – I raised it internally a while ago and it received attention at every level – but it isn’t really where it needs to be yet.
You’re right about the structural limitation in the MySQL QA process. It has been receiving attention at all levels and is improving but it’s not remotely close to what it needs to be yet – and that is universally accepted within the company. It’s one of the cases where community involvement at the earliest possible alpha and beta stages is vital.
If anyone wants to work at MySQL, working from home, on QA, send your resume to james at mysql.com and I’ll pass it on. MySQL is looking for good people in this area, anywhere in the world.
Hi James,
I’m fine with the concept that the community needs to be involved in order for the new features and new releases to be tested properly. However, where this goes wrong is that MySQL moves on to calling releases “stable/GA/production/new term of the week” when the community (the important parts that would really test those features) has NOT been involved yet.
How many large installations are running 5.0, using triggers, stored procedures, stored functions, views, prepared statements, AND replication today? Do you know of any?
jcole’s weblog » Blog Archive » MySQL 4.1 and 5.0: Prepared Statement Leaks
Jeremy,
There’s a bit of a conflict between testing by those users before GA and the desire by them to have a GA release for first use. Or even their pretty common desire to wait well after GA so they don’t find the initial bugs which always follow the much wider post-GA user set. Some things are being done to get more aggressive testing pre-GA but more would definitely be good.
I don’t look at users by features they use so I don’t know if any are using all of those you named. Some are using a fair range of the features and some of those did have significant problems shortly after GA.
It’s fairly likely that 5.1 will be what we’d all have liked 5.0 GA to be. Far less changed code between 5.0 and 5.1 than there was between 4.1 and 5.0, so it should be substantially cleaner at GA.