September 19, 2014

MySQL Quality of old and new features

Recent couple of days our team was pointed to number of bugs in MySQL 5.0 which again seriously shakes the confidence in both MySQL Quality Control and bug fix promptness.

Let me just take couple of bugs as examples:

Triggers broken with auto-increment columns for Innodb tables (bug 26316). As you can see this bug is reported in February – over 6 months ago and it is still in verified state even though it has “serious” severity.

ORDER By DESC broken for Innodb tables (bug 31001) This is also very interesting – the VERY basic functionality gets broken and passes quality control. And why it is broken ? Because minor optimization was implemented in MySQL 5.0.

Surely Baron is right Bug fixes are not much different from new features in terms of possibility of breaking things.

These raise few questions in my head:

What is MySQL Current Bug Policy ? A long time ago MySQL had Monty’s bug policy of “no known bugs in release” when it was changed to “no known serous bugs in release” and I think later it died off completely, at least in practice.
As you can see now serious bugs can remain unfixed for over half a year. Furthermore there is no good errata documentation which would outline which things have known problems which MySQL can’t fix fast.

What is the state of MySQL Quality Assurance I would tell both bugs are very basic but especially second one.
So how it happened so it was not caught ? I believe the problem is serious lack of concurrent stress testing and the fact MySQL Test Suite does way too many tests only for MyISAM storage engine which well allows bugs like this to slip through.

What can we learn from this ?
I think we should continue to be cautions using new features which first came in MySQL 5.0 They are now much better than were when MySQL 5.0 just came out (aka minefield) but still they are not at quality levels of base MySQL features.

Plus we should be careful and not to take minor upgrades lightly – this is one of many things which was broken along the way in MySQL 5.0 release so being careful is the best.

It also may be good idea to sample queries from your application together with sample database and package it as a test suite to ensure queries which you’re using will not break. You would not catch trigger bug this way but it can be helpful for basic query bugs at least. Plus it is not that hard to do.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Nico says:

    Hi,

    I have to decide between PostgreSQL en MySQL for a serious application. Well I was considering MySQL, but now when I see that basic
    things like ORDER BY and multiple inserts through triggers are not even working well, I cannot consider it a serious option anymore.

    Regards !

  2. Lukas says:

    From what I hear behind closed doors, both testing and better documentation of how versions change behavior are getting addresses. But its important that you post stuff like this to keep the pressure high. This kind of stuff is really horrible!

    For the issue of storage engine testing I think this is one of those issues that really makes it clear that pluggable storage engines are not the silver bullet. Of course all the storage engines use common infrastructure, but to me it seems that in the grand scheme of things using multiple storage enines is like using multiple different RDBMS products, you just happen to use the same interface. As such you need to know about tuning, need to allocate memory and other hardware ressources to each and finally you need to know about bugs and quirks in each of them. As a result, this means that each storage engine that solves a real world problem increases the know how expected of a MySQL DBA. The question is if our brains will scale with this approach?

  3. mike says:

    Thanks for posting this! Now I’m glad I’ve been waiting to upgrade.

    Sometime being a friend of someone means being their enemy when
    you see them doing something things that are not good for them.

    What I think that statement means in this context is that I see that you really caring about
    MySQL and you see that they seem to have wandered away from what the
    product means to people and what people really care about.

    For me it is performance and a good alternative to the hi-priced commercial databases.
    I think this is also one of your soap boxes and I encourage you to keep at it.

    Hope that makes sense.

    Thanks,

    Mike

  4. peter says:

    Lukas,

    Yes I also have been on developers conference and I see and hear things are being improved both in terms of QA and in terms of bug fixes compared to early 5.0 release stage.

    However things like this (and there were plenty of other breakages in recent MySQL 5.0) show a lot of things still needs to be done.

  5. Sergei Golubchik says:

    Peter, you’ve surely noticed that bug#26316 (Triggers create duplicate entries on auto-increment columns) is assigned to Sunny Bains since May 28th . There’s not much we can do to have it fixed :(

  6. peter says:

    Sergei,

    I can see that. However it is barely an excuse. MySQL officially supports Innodb which I assume means it has contracts with Innobase/Oracle which ensure bugs get fixed within reasonable time frame.

    Not to mention if Innobase does not have resources MySQL could provide approriate patch, which previously happened more than once.

  7. Jeffrey Gilbert says:

    The order by bug appears to have been patched for the next bundled release (5.0.50?) and appears to have shown up at 5.0.48, but isn’t the current bundle only 5.0.45? Based on the public releases and the bug tracker it looks as though it was patched between their main linked builds. If this is not the case, feel free to correct me.

    i love the idea of moving to 5.1 soon but i hate the idea that it wont be stable enough for reliable data storage and performance in a production setting. i’d love to take advantage of the new features and fixes around clustering, table partitioning, and scheduled tasks, but from past experiences i definitely find it best to wait several minor revisions later to get something tested by someone else’s time rather than mine ;)

  8. peter says:

    Jeffrey,

    MySQL 5.0.48 is latest and greatest enterprise release:
    http://dev.mysql.com/doc/refman/5.0/en/releasenotes-es.html

    Community releases are not published for all versions as source tar balls or binaries.

  9. Jeffrey Gilbert says:

    peter,
    thanks for clearing that up. i’m not an enterprise consumer. the 500 dollar version gives me too little and the 5000 dollar version costs too much. truthfully speaking, in addition to the slow query log, all i really need is a query logger that shows me anytime a tmp table is written to disk, or sort_merge_passes is incrimented so i can optimize uncaught queries which might not show up under the slow query log during low points in traffic. such a tool would be a life saver.

  10. peter says:

    Jefferey,

    MySQL is GPL so you can get build from the same sources as MySQL Enterprise for example here:
    http://www.dorsalsource.org/

    For Support or monitoring services you need to pay but you can get binaries and sources for free.

    Regarding your logging with – good idea. I also wanted this for long and may be we will be able to add this to our logging patch.

  11. Odin says:

    Doesn’t this basically kill any desire for large corporations (especially financial institutions) to use MySQL?

    If MySQL likes to compare itself to the competition for such situations then it’s laughable.

    The most basic features of the SQL standard not being conformed too is just plain embarrassing.

    Of course Mr. Cheap AKA Me continues to use MySQL! :-)

  12. Peter,

    Sunny does have a patch to http://bugs.mysql.com/bug.php?id=26316, but we want to check the patch carefully, not to break other AUTO-INC functionality.

    Regards,

    Heikki

  13. Jeffrey Gilbert says:

    peter, re the patch, that would be an excellent addition. maybe it wouldn’t find the needle in the haystack, but the haystack would get much smaller. surely this would be something worth adopting with minimal performance hit.

  14. peter says:

    Heikki,

    Thank you. I understand. Still over half a year for bug fix is a bit too long.

    I understand there are design bugs which are extremely complicated, and a lot of such simply were not fixed in given major release, but they often were documented.

    Bugs like this definitely should have made it to errata so people would know it is known bug which takes a long to get fixed.

  15. Dan says:

    Maybe finally people start to realize that MySQL AB is not up to the task, nor their product, regardless of how much they’re touting this.
    This hacker mindset, quick and dirty way, has been long set in their company with foreseeable effects. They are just starting to collect the “rewards” now. I know I shouldn’t quite happy about this but given the amount of frustration mysql gave me lately, it’s starting to feel a little better to know the company starts to fail so miserably. Maybe things are going to in the right direction though after these problems. Maybe with a complete re-write of the entire MySQL RDBMS, maybe even in java. And maybe they quit this stupid approach of multiple storage engine architecture, which is a complete drag.
    Once I asked one of their proeminent employee what’s the MySQL AB/product position on the market in comparison with the other big players( I had in mind Oracle when I asked that). He answered that “we’ll get there”. God help that’s set to happen in my lifetime.

  16. There are glaring Serious bugs in production. Any data-corruption bugs that break core functionality like UPDATE should be handled immediately http://mysqldba.blogspot.com/2007/08/oooh-really-bad-bug-in-4123-4124b.html To be honest it’s embarrassing. Two years ago, corruption issues would have a special revision and a hosted download of the fixed binary withing weeks if not days. Now well if you have a support contract you will get a special build which is ridiculous. No data corruption bug should make it into a production release-this new don’t care policy, conveys that mySQL is a shitty product-to old and new customers alike.

    In my opinion people who pay money for mySQL should be given piece of mind that mySQL itself will not cause data-corruption. Any updates are to fix non-serious bugs or improve performance. The people want SPEED and Reliability, they will pay for that! To feel confident that mySQL will not cause corruption is to release the fixes and use the community as the testing ground. If it works for the masses then it will work for the specific paying customer.

    If I where running things; All versions are data-corrupt stable and no Serious bug makes it into production. If so, an immediate release is made. Enterprise has a few optimizations that make it 20% faster then Community (like fix the Optimizer, Grammer Parser, etc). People will pay for Enterprise if they want the speed and support, while the community is happy for a free stable product.

  17. peter says:

    Indeed Dathan this looks like rather basic bug which was broken in 4.1 which is so old release I would not expect it to happen.

  18. Petri Mäkinen says:

    People will need to realize instead that databases are a horrible idea, which in itself leads to overcomplicated designs and codebases. Which in turn lead to more horror for clients. Haven’t they suffered enough?

  19. Dan says:

    For Petri Makinen:
    What alternative do you have in mind? Because I’d agree with “crappy databases are a horrible idea” but not with that generic statement of yours.

  20. she says:

    i think part of this is to blame with MySQL move to corporate levels… which means more looking for new “killer features” and less on the docu+bug fixing parts….

    but i think we shouldnt put too much emphasis on it, the base idea is to never become too dependent on one database!

  21. she says:

    “People will need to realize instead that databases are a horrible idea, which in itself leads to overcomplicated designs and codebases.”

    Oh, i must say this is a WRONG conclusion.

    The basic and best advantage of a general database is that it can keep ALL your stuff together.

    I maintained a lot of standalone .html and .php with only very few sqlite3 databases.

    Although it works, it was not good enough to maintain (i switched to use ruby now anyway, since as a language, ruby is a lot cleaner than php. Which makes it easier for me to maintain it)

    The database that I now use (mysql… uuugh i know, but the thing was i found a lot more user-contributed tutorials for mysql and sqlite, than for postgre….) keeps the whole dataset and generates “pages” on the fly (with a set of ruby scripts).
    No more static html pages (and if, then they will be autogenerated) and no ugly php scripts (except for some legacy scripts which i am too lazy to get rid of… but over the years, i think even these will disappear)

  22. James Day says:

    Peter, because of that order by bug 5.0.48 was withdrawn from availability to Enterprise customers on 13 September, the day it was reported. Each customer who had downloaded it from MySQL was notified of the problem. It still shouldn’t have happened but it’s the best handling of a post-release problem like this that I’ve seen so far.

    For the week or so it was available from MySQL to Enterprise customers 5.0.48 was an MRU and our recommendation for MRUs is that customers only use them if they need a bug fix it contains, preferring the QSP releases otherwise.

    There’s one other change that I’m expecting that you’ll probably like: documentation of regression bugs from the version in which we find they were introduced to the version in which they are fixed.

  23. peter says:

    Thank you James.

    Good to hear it was withdrawn and customer are notified this is indeed the service one should expect if one is paying for. And yes indeed documentation of Regression bug would be very important, though I do not know how it will be available.

Speak Your Mind

*