Why I find MySQL to be so bad

MySQL is the most used Open Source database engine. I have personally used it for almost 10 years. I worked with versions 3.x, 4.x and 5.x of MySQL, but I also used other database engines, some of them handling the same "data-set size" as MySQL, while others have been smaller databases (mostly Desktop based databases such as SQLite, MS-Access and Paradox).

This is the first part of my complaint about MySQL and why I dislike it.

I've found a few major problems with the way MySQL implement indexes. For example, if ones takes Firebird, PosgreSQL, Access, Paradox, SQLite, MS SQL Server and few other databases, they all save the last value of the index in a file. Usually with sequences, but not necessarily. MySQL does it differently:

  1. Save the last inserted id in RAM.
  2. If the server just started (reboot/restart the server), it will take the last index from the table. If there are no items in the table, it will assume that the index of the newly-inserted id, will be 1.

The correct way of doing it is to store the index position in a file regardless of the state of the table. If we wish to start from the first possible index (or from the last inserted id), we should perform maintenance on the database and then apply this change (usually part of compressing data inside the database, but some databases might do it differently).

What else ?

Each SELECT statement locks our table for other SELECT statements! I have multi-threaded applications that perform many SELECT statements on the same table but with different queries, on the same connection pool, however I needed to synchronize many of the SELECT statements just because I had deadlocks when performing several simultaneous select on the same table.

A normal lock will perform a write lock on a select (or no lock at all) not a read lock !

Two issues are not enough, right?

Well, every release of MySQL sub-version create new API version and changes, so for 5 and 5.1 you need two API binding. That's not all. On 5.1, each sub version also creates new API changes, so every version (regardless of how small it is) of MySQL has a different API. That's a nightmare for maintaining communication with the database.

On the other hand, Firebird (which is derived from the Interbase 6.1 source code), introduces additional API routines on each major version, but does not touch the working API. If they do change something, they do it on a major version and not on a sub version. So the bindings for Firebird 1.0 works equally as well with Firebitrd 2.1 (at least for me).

I know, you are going to say that I need to be binding to a higher level of API such as DBI (in Perl) or might even ORM (it's even higher the the DBI approach). However, the lower level will still be changed, so it will not solve the problem, it will only make it harder to solve.

When MySQL finally releases new versions of the database (it sometimes takes them years between patches), they do not release a fully working database, but fatal bugs are found soon enough and every new sub version that comes out, will soon enough have patches to fix them. It seems that 2-3 years are not enough and they require additional time to workaround issues. How can you trust a database like that ? How can you make absolutely sure that your data will not be destroyed by a bug on every new update ?

OK, so you probably think that backups are in order. Well, the official MySQL backup system is part of the SQL language, but restoring the data from the way it saves to backup does not always works. Even table structure are not always saved properly, not to mention the data itself.

The mysqldump is the most used tool for that job. However, if you wish to store binary blob data (not recommended in any database, by the way), this tool will not be a good choice, unless you can somehow use MIME or a different way to place the data of your blob as text and revert it to binary when dumping the data back again.

These are not all of the problems of exporting our database. If we have foreign keys, we must tell MySQL not to check their existence in order to import the data. MySQL can't handle foreign keys in import and export of data properly according to the official documentation (and practice).

If you wish to save and restore BLOB data as MIME (for example), you cannot really do it automatically. Unlike Firebird/PosgreSQL that can be extended by 3rd party tools (see User-defined Functions), making the database work differently from what it was originally designed, so we can get more from our data, MySQL cannot. You cannot extend the database, only write a tool to do it for you (importing complex data).

An additional problem with MySQL is the lack of creating new data types (also known as domain), or supporting more data types. For example in Firebird I can create a new data type. We can take a "varchar" data type in the length of 10 chars, and make a new data type out of it. Now every time we'll need a "varchar(10)", we'll use that data type in the database. Firebird knows it's an alias for varchar(10), but we do not need to write it every time. So it saves us time, and make our data type better in understanding what it does,  But then again, we're asking too much from MySQL I guess.

So what does MySQL support ? how about "DROP DATABASE"? Well sure it has support for it, but:

  1. You must remove any Foreign keys on your own, or it might have problems with the execution of the command.
  2. You might be required to continue the work of "DROP DATABASE" by hand (according to the docs) because it might not remove everything.

Wow! MySQL is such a reliable engine, that it does not want to remove your data even if you ask it to do so…

Now when MySQL finally works like we want it to, we might find ourselves in the following situation: when we use a bigger data length/size than we are able to store, it just truncates it, and not always the way we will expect.

Let's see:

CREATE TABLE test1(name VARCHAR(4), value TINYINT(2));

INSERT INTO test1 (name, value) VALUES('Firebird SQL', 1996);

The result is as follows:

SELECT * FROM test1;
+--------------+
| name | value |
+------+-------+
| Fire | 127 |
+------+-------+

As you can see, I can actually find that name was truncated (I hope), but the value contains the roof of what tinyint can be. And it only gets worse from here on. Data types such as sets actually can place empty data instead of the actual content, and it can be even worse.

The thing I'm expecting is that MySQL will report an error or something regarding the value that I told it to add, but no, it just add it and does not say anything. I believe that on data manipulation, the database is the tool that should do it rather then my code (it was designed to work better with data manipulation), but I guess my point of view is wrong. So I just need to add extra code to handle the data itself before it is stored. (And I'm not talking about filtering bad chars, for that I have binding support and other strategic ways to store/use that data.)

You think that's all right ? well, wrong !

I do not want to even talk about problems with JOINS that have existed for many years and versions in MySQL, because that requires a post of its own, but what about optimizing our SELECT statements?

Let's see:

Development is ongoing, so no optimization tip is reliable for the long term.

Well I guess it just can't handle this. I wonder if many instances of the Slashdot/Digg effect are due to lack of proper query optimization. Even though there are many other factors to take in consideration (web servers, processes that are inside the server that are running, memory, load balancing [if it exists] etc.), but at the bottom line I guess is that MySQL is not so good for production if you actually need to optimize it.

That's not all I can say, but this post is getting way too long. Thus, more issues that I have with MySQL will be written in post #2.

Right is right, even if everyone is against it; and wrong is wrong, even if everyone is for it

William Penn

רשומה זו פורסמה בקטגוריה טכנולוגיה, מסדי נתונים, קוד פתוח. אפשר להגיע ישירות לרשומה זו עם קישור ישיר.

20 תגובות על Why I find MySQL to be so bad

  1. nadavkav הגיב:

    did you report any of these issues to mysql developers ?
    if so, what was there reply ?

  2. ik_5 הגיב:

    All of my claims here are documented issues long before I ever discovered them.
    So if you will go to the documentation of MySQL you will find the exact things I'm talking about here.

  3. פינגבק: Posts about Digg as of March 15, 2009 » The Daily Parr

  4. nadavkav הגיב:

    yes, i can see.
    let's hope it would change for the better , soon :-)

  5. ik_5 הגיב:

    After 6 versions, I think it's time to move to different databases.

  6. פינגבק: Posts about SEO as of March 15, 2009 | Shirasmane

  7. Shlomi Noach הגיב:

    Hi!
    I respectfully disagree with many of the issues written here. Where should I start?
    SELECTs blocking each other – not true, not even in the MyISAM storage engine, certainly not in InnoDB.
    With InnoDB you can have concurrent writes (so long as not on same rows).

    With regard to release cycle: sure, there are bugs. They are also open, documented and known. My advice to you: whatever the product is, don't use it the second it is released. It's the same for all products. With MySQL you can be quite certain if a critical bug emerges – it is fixed quickly.

    I fail to understand the issue with backups. Could you support what you wrote? I'm using mysqldump, raw copy, FS snapshots, InnoBackup – there are lot's of backup methods – and they work well. When you complain about something – it's best to be clear about it and provide an example.

    Data truncating: it's a pity, I agree, that the default allow for implicit data truncation. I've reported a feature request for that. But please be aware that you should set sql_mode to 'TRADITIONAL', and have a very stricty database.

    There are many things more to write. I do not disagree with everything you say. I have found and reported bugs in MySQL as well. It is not perfect, but I have yet to see a perfect product. There are so many upsides to MySQL. See what Google Patches do. See what Percona does. Using the open source model, they add requested features, some of which are scheduled to go back into the baseline. There are numerous utilities to help out with common tasks.

    You may say "Hey, but nobody told me", and you would be partly right. Sure, it takes experience to make a good MySQL server installation, finely tuned. But it would take experienece to have the same on Oracle, Postgresql etc. With MySQL, you have a huge users base. Like you said – anything you've encountered (when true) has already been reported. Some has already been fixed. Somewhere smoebody has the answer to your problems.

    MySQL is missing a lot of features. Sequences, function indexes, partial indexes, hash join, etc. etc.
    It's not an Oracle competitor. My experience as a consultant shows it works well for many companies, some very large.

    Anyway, you may have your own bad experience, which is fine. One thing is sure: a "Why I find *** to be so sad" post can be written on any database or otherwise any product.

    Regards

  8. ik_5 הגיב:

    For Java applications that I wrote using ConnectorJ (That is developed by MySQL) the lock of the select statement happened more then once or twice with different versions of MySQL, so the actual way MySQL Behave is different then the way the documentation claims it even worse…

    The problem is not the time that it takes to release, but the unfinished release of the product. And if that's not worse, each new sub version works differently so if your Linux distro upgrade for you from MySQL 5.0 to MySQL 5.1 some things works differently, and many of them will contain bugs.

    I have a lot of problems with backuping MySQL when I'm using forigen keys, or copy the database directory or trying to do different things to backup (using the documented ways). I find many times that the backup system does not function as it should, and everytime I looked for the reason, I found an open bug, or some remark in the documentation (of users) regarding this issue. I find it weird that people ignores all of the problems with MySQL implementation of foreign keys and the lack of proper way to sometimes even move between database sub versions without loosing data. It happened to me, and I know few other people that it happened to them, so it's not just me, and the comments of other users makes it clear that you have problems with backups and it's not so "simple" as people might claim it is.

    There is a post by Artyum in Hebrew (http://art-blog.no-ip.info/newpress/blog/post/202) that claims that you do not need to become a DBA person to work with MySQL, I guess he is wrong …

    I'm not claiming that other database are good, I just claim that MySQL is worse.
    I worked with enough database engines to see difference approaches on how things works. MySQL's patches by third party companies just emphasis my claims, that it's not good enough on it's own.

    I'm starting a campaign to adopt Firebird instead of MySQL, and this post is part of it.

  9. Shlomi Noach הגיב:

    Hi,
    Please understand that if it were as you say — that SELECTs block each other — no one would ever use MySQL. ever. I understand there is some problem on your application/JDBC – don't assume it's a MySQL 'feature'

    I'm working as a MySQL consultant, and so I have seen many installations, products etc. I've upgraded from 4.0 to 4,1 to 5,0 and to 5,1 – I have never failed an upgrade. If you have issues with the direct API – that's another issue – but you really should work on a higher level. Leave the API for the connectors developers, otherwise don;t complain when it changes. It's only natural.

    Again, there are many backup stuff to know about. It is certainly possible to backup a DB with foreign keys – there is more than one way to do so.
    You describe a situation in which some people have issues with backups. While there could be bugs, etc., please recall that *no one* would be using MySQL if it would not be able to backup it properly.

    This relates to the next part: of course you need to be DBA to work with MySQL!! Whoever says differently is not thinking it through.
    Sure, you can write up a PHP app without knowing anything about MySQL administration. But the moment you say "backup" or "optimization" or "replication or "HA" etc. – that's the point where it should be obvious you're not playing anymore – but managing a database. No DB is a toy. Of course you need to know how to handle it!

    Still it is a common agreement that MySQL is easier to set up. I don;t know about the rest of it.

    Good luck with your Firebird campaign!

  10. ik_5 הגיב:

    Shlomi, I'm using open source and Linux for many years now, and one of the things I discovered was that it does not matter how good or bad things are, it's all about the buzz.
    I worked few years with Delphi. It was the best development environment ever existed until the Microsoft .NET era, yet most of the market choose VB etc.. not because it was better, but because it had more buzz into it.

    That's the same with MySQL. PosgreSQL is Superior in any way over MySQL. But configuring it and working with it is not as simple using MySQL.

    Firebird (that have it's own flaws) also contains a lot more then people think (the approach of Firebird is to use UDF on every thing that is missing for you, and there are/was companies that actually made/makes money out of it). Yet most people does not know anything about this database.

    At the beginning of the problems with ConnectorJ, I also thought that the problem existed on that component, but when I arrived to dead lock even PHP code that used the same tables was unable to do selects. I'm talking about the 4x series (I still have a production server that uses it for the past 3 years).

    Regarding the API, I do not always use just the norm tools such as Java, Ruby, Perl etc.. I also use FPC and few other tools. Now on commercial world you always have a magical "someone" that bind things for you, but in the open source/less popular tools it does nor work like that most of the times.
    I would really love working only with ORM, but life is not that simple :(

    Backups should be simple, because they are important. If you have 1001 ways to do it, you will find 1002 people that will do it wrong on every way. Sure we all require DNA person, but I'm a freelancer and I can not afford to have DBA person, so I'm learning how to manage databases. There are many companies with the approach of in-house maintenance without DBA people, and the "simple" way of MySQL always blow in the face when people try to work a but more then just simple select statement.
    The choosing of data engine is also not so simple task. InnoDB is nce, but it's not the answer for everything. So does MyISAM. In Hebrew we have a saying that from too much tries you can not see the forest. That's what happen to many people using MySQL (I think you know it better then me, because you make a living from it).

    I'm reading planet mysql and few other mysql resources (I also encountered your blog there), how can you explain that something like half (never did the math) of the posts are on how to solve bugs or workarounds ?

    I understand that you make your living from MySQL, but at te bottom line something is really wrong with the database. If you start googeling a bit, you will find a lot of complaints posts about MySQL (I used some of them to make sure I'm not the only one that suffers from problems I'm having).

    My next post will be actually about some bad issues with Firebird, but I do think that they are less worse then the problems with MySQL.

  11. Shlomi Noach הגיב:

    Hi,

    I do make a living of MySQL, but 'm careful not to make it my point. I've no problem with acknowledging the many downsides MySQL has. To some of your complaints I didn;t respond because they were correct.

    I myself am writing utilities to overcome the MySQL limitations.
    The thing is: MySQL isn't Oracle or DB2 or SQLServer. They're not on the same level.
    So it's a simpler DB – but it's not simple at all!

    I go against the notion that "everyone can work out a MySQL instance without any prior knowledge and have a godo day" – not because I expect people to call me for help – but because a DB is one of the most crucial parts of your system. When it goes bad – you are in a very had place.
    I had a company flying me to Europe just to help them out in fixing the database – because someone who is not an expert dba gave the wrong advice.

    That is to say I do not believe MySQL to be a lightweight product. One should really know her whereabouts with it.

    I agree that between MySQL and PostgreSQL, the latter was fasr superior over the years. Nowdays they are roughly on the same level in terms of features. What did MySQL catch on? Easier to install, as you say. Better push from a well organized company (who also provides trainings, books etc.)
    Try to find an up to date book on PostgreSQL in amazon. Then look at how many books there are on MySQL. It's a snowball.

    Is there "something really wrong with the database"? I think this is an exaggeration
    It's far from being perfect – but it *good* and works well for many companies – real companies with real products which need real maintenance and performance.

    PS ORMs are not fun to work with –at all–/

    Regards

  12. Shay Ohayon הגיב:

    Hi,
    I am no MySQL nor DB administration expert, but I have some experience with it, and one of the things that I've seen as a sort of "bug" in MySQL and also bothered me while configuring it, is replication. from what I've read (sorry, but I dont have the links with me right now) replication in MySQL is a bit of a headache, I personally had the bad experience of debugging for a couple of days a slave replication and founding various problems like for example temporary tables. MySQL replication does not know how to handle temporary table (and it is also "known" by MySQL but neither is it documented on the "Replicatoin" chapter of their administrator's manual nor anywhere else – so you must try and fail to notice it). While doing a replication of a Database and when the slave "meets" a temporary table, it does not know what to do with it (since the table was not created from the beginning and is not going to be created at all) and then it fails. You have no idea that it happened until you check the status of the slave server and only then notice that something went wrong. The only workaround for this issue is telling the MySQL daemon to ignore this errors and continue the replication.

    Other thing that I am not sure about is what to do when the master crashes. Let's say that this scenario happens and I route all the queries to the slave server; what happens next? how do I update the Master once it is up again about all the changes that happened on the slave and how do I tell the slave to "become a slave again" without downtimes or loss of data? theoretically speaking (and *please* correct me if I am wrong, since I would like to know if there is an elegant solution) is to check the difference between the two (raw) binlogs of the servers and merge the changes manually and then reconfiguring both server, the master and the slave to get back to the original state.

    Bear in mind that, as I said in the beginning of this post, I am not a database expert, just learning while applying it.

    thanks,
    shay

  13. פינגבק: Pythian Group - Blog

  14. Shlomi Noach הגיב:

    Shay -
    with regard to re-syncing a failed master, one of the common solutions is using an Active-Passive Master-Master replication.
    In this configuration both servers are replicating each other. Therefore, if the crash is a minor one, there's not much to "tell" the old master – it can catch up automatically.
    If the crash is more serious, and data is lost on the original master, you have no choice but to restore it all. If you have yet another slave – you can utilize it. Otherwise, you need to duplicate the existing server.
    If you need to do that online, you can use mysqldump –single-transaction (for InnoDB), Innohot-backup (Innodb), mylvmbackup (requires LVM).

    Shlomi

  15. Shlomi Noach הגיב:

    Shay,

    Temporary tables *are* replicated. See:
    http://dev.mysql.com/doc/refman/5.0/en/replication-features-temptables.html

    However, it should be clear that you would not be able to actually *see* them on the slave, since connecting to the slave uses a new connection, other than the one in which the temporary tables were created.

    If I've missed your point, please elaborate.

    Regards

  16. Shay Ohayon הגיב:

    Shlomi, thanks for your input! I will study this and try to apply it on my servers.

  17. פינגבק: Log Buffer #140: A Carnival of the Vanities for DBAs | WORDPRESS EXTENSIONS-PLUGINS-THEMES-TEMPLATES

  18. פינגבק: Things that bother me a lot in Firebird SQL « לראות שונה

  19. פינגבק: Firebird News » Things that bother me a lot in Firebird SQL

  20. RaiulBaztepo הגיב:

    Hello!
    Very Interesting post! Thank you for such interesting resource!
    PS: Sorry for my bad english, I'v just started to learn this language ;)
    See you!
    Your, Raiul Baztepo

כתיבת תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s