Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

[OT] Why I don't use Mysql for new projects

by moritz (Cardinal)
on Jul 10, 2008 at 11:36 UTC ( #696677=perlmeditation: print w/ replies, xml ) Need Help??

This is a very controversial topic, so I'll start with a list of disclaimers:

  • I didn't want to write this at first, but Arunbear and barrd asked me to do it. Since I ranted a bit about mysql I felt obliged to justify my statements
  • I used to like mysql, and I still use it for older projects. The reasons below aren't enough for me to convert old projects, but enough to use postgres for new ones
  • I very much appreciate the effort from the mysql developers, and don't mean to insult them. I just want to point out that it has weaknesses.
  • Many of the problems below have fixes, but they aren't fixed by default (for example you have to set some variables to enforce sane behaviour). I list them nonetheless, because you first have to know about the gotcha, and many people fall into the traps at least once before changing the settings. IMHO sane defaults are very important. I can mostly work around that stuff, but I see no reason why I should have to.
  • I tried to list only what I experienced myself in order not to spread FUD.

Data Integrity

Inserting invalid data

If you insert invalid data, mysql does not complain. (Unless you modify the sql_mode variable, available starting from version 5.0 ). If you insert an arbitrary string into a date column, you'll just get a default value.

Transactions

Not all backends supports transactions. If you use transactions that involve tables with other backends, you'll get weird behaviour on rollback - all innodb tables will roll back, all others won't.

Also stuff like ALTER TABLE and CREATE TABLE don't roll if done inside of a transaction. IMHO if it can't roll the changes back, it should forbid them inside a transaction - otherwise you risk losing your data.

Foreign keys

Last I looked, foreign key constraints didn't work. This seems to be fixed now innodb, but requires migration to innodb.

Character Encodings

Mysql offers a myriad of options for handling character encodings in text columns, but I could never get them to really work unless I set everything to the same encoding. I don't know if that's my fault mysql's, but ususally I'm not too bad at reading documentation and setting things up.

Handling Failure

Just the other day I tried to import an sql dump - not a big deal you might think:

bzcat dump.sql.bz2 | mysql $table

But, somehow, it looped. Ususally the import takes about five minutes, after 20 minutes I hit Ctrl+C and aborted the import. For some other reasons I had to reboot the machine, and during that reboot mysql didn't start:

Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

Very helpful error message indeed. It turned out that my root partition was full. But it didn't tell - it just looped. Turns out that big chunk on disk were the mysql log files (not mysql's fault this time), and I wanted to delete them. So how do you delete mysql log files? You type mysql and truncate the binary logs. No, you don't - because mysql didn't start up in the first place, because the disk was full.

So instead I just deleted the log files - and mysql wouldn't start, because the log files were missing. Since I was offline at that time (no chance to google for anything) the only thing that I found I could was to reinstall the mysql server. Just because my partition was full.

I regularly hang out on #perlde on irc.perl.org, and many read quite a few reports where mysql didn't behave very gracefully in error situations.

Documentation

The mysql documentation is quite good, but by Debian's standards it's non-free, so it can't be included in the distribution. That's a big disadvantage if you're often offline.


erix posted this link in the CB that explains the reliabilty issues with mysql in more detail.

Comment on [OT] Why I don't use Mysql for new projects
Select or Download Code
Re: [OT] Why I don't use Mysql for new projects
by tantarbobus (Hermit) on Jul 10, 2008 at 12:19 UTC

    MySQL sucks! But then so does PostgreSQL and Oracle and Access. I have not played much with Firebird, SQLServer, Sybase, Impress, or DB2, but I am pretty sure that each of them suck too -- along with every other database in the world.

    Every database sucks in its own way. A large part of working with databases is dealing with the parts that do not work the way you want them to :)

      If relational integrity is by far and away the #1 concern, which open source DB should one choose? Firebird or PG?

      I get the impression that MySQL is designed and used more as a data storage system than as relational model system. Correct?

        No, not correct. MySQL supports the same data integrity features as other databases.
Re: [OT] Why I don't use Mysql for new projects
by Mutant (Priest) on Jul 10, 2008 at 13:41 UTC

    Funnily enough, we're currently looking at Postgres vs MySQL at work, since Oracle seems to be sucking up higher and higher percentages of our budget, and we already use open source solutions to a lot of other problems, so it seems to make sense.

    I haven't really used Postgres in about 4 years, but even then I preferred it to MySQL. It seems that in that time, while MySQL has made up for a lot of it's shortcomings, Postgres has continued to improve as well. It's now ever surpassed MySQL in the one thing it used to trail on - performance.

    On top of that, Postgres is true open source, not backed by a company like MySQL. InnoDB (which is what gives MySQL a lot of it's improved data integrity) is closed source, and licensed from Oracle. What happens if Oracle decides to terminate that license?

    The only thing is MySQL is deployed a lot more widely than Postgres (which is somewhat analogous to PHP and Perl). I'm using MySQL for a personal project of mine at the moment, because my hosting provider has it available by default. Maybe this will start to change, especially if MySQL ends up moving in a different direction in the hands of Sun.

    Some intersting reading on the subject:

      InnoDB is not closed source. MySQL is a fully open source project. You don't need to get it from MySQL AB.

        I don't think I explained my point of view on that particularly well. What I meant was MySQL is backed by a single commercial interest (and as of this year, that commercial interest is a Big Corp). Somehow, someone has to make some money pretty directly off MySQL, and that someone has a lot of influence on the direction of MySQL. Much more than, say, IBM could ever have over the Linux kernel, despite the fact that they put money into it.

        To me, that's a conflict of interest. Plus it creates uncertainty about what might happen in the future. Sun's already talking about closing the source of some of the tools (I doubt they will ever close MySQL completely, but still...). If there's a choice, I'll usually go for the OSS project that is not primarily controlled by a company. e.g. Perl over PHP, Debian over Red Hat.

      Pet peeve: interestingly enough, "funnily" is not a word. Not even close. "Interestingly" works because it ends in "ing", and so adding the "ly" sounds ok. Also words ending in 'e' can work, as in "strangely", or just words ending in a consonant like "thinly".

      "Funnily" sounds clumsy and made up, or possibly sounds like it's meant to mean "something vaguely funnel-like".

        Well you know what, that's a peeve you can get rid of!

        As it turns out, funnily is a word, more specifically, an adverb, meaning "in a funny manner", as any reliable dictionary will tell you.

        • another intruder with the mooring in the heart of the Perl

        My pet peeve: amateur grammar nazis.

        As grinder kindly pointed out, "funnily" is a real word. Even if it wasn't in the dictionary, that's not what makes something a "real word". It's common usage. And "funnily enough" is a reasonably common phrase, at least where I come from (NZ originally, currently UK).

        I have to also wonder if this trick would work on you.

      MySQL AB makes almost all of its money from the following sources:
      • Training and Certification
      • Support
      • Consulting
      • Licensing under very specific circumstances
      And, from what I understand, the licensing revenue is a pretty small part of the pie. Not surprisingly, the first three are the same ways that RT, Shadowcat, and Stonehenge makes their money. The Postgres Foundation makes money from donations. I suspect many of the organizations that the PG devs live in support themselves in similar ways as the companies previously mentioned. I know that this is the case for both MySQL and Perl and Ruby.

      My criteria for good software:
      1. Does it work?
      2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Re: [OT] Why I don't use Mysql for new projects
by perrin (Chancellor) on Jul 10, 2008 at 14:35 UTC
    You say that MySQL has solved problems but you won't count it because it's not the default? The reason it's not the default is that people expect to be able to upgrade from older versions without having to change their code. I think this is a fair expectation.

    If your node was named "Why I will use InnoDB tables and strict SQL mode for all new projects" it would make just as much sense. There is no reason to start a new project with MyISAM tables and the old SQL mode. InnoDB with foreign keys and transactions has been production ready for 7 years now!

      You say that MySQL has solved problems but you won't count it because it's not the default?

      No, I say that solutions of these problems aren't as valuable as they could be if they were default.

      Discovering glitches and then finding that they have already been solved but that I didn't know about these solutions, always leaves that nasty feeling that I'll come across more of those.

      Sane defaults are important. Very much so. The Postgres 8.2 manual has about 1700 pages (A4), I'd expect mysql to have about as much documentation. You can be sure that I won't read them all before starting to use my db engine. If the defaults can easily hurt me, I'm lost.

      The reason it's not the default is that people expect to be able to upgrade from older versions without having to change their code. I think this is a fair expectation.

      Yes and no. Backwards compatibility is valuable, but it shouldn't be the first ruling principle. When our notion changes of what is sane and what not, we sometimes have to break something. Perl does that from time to time as well.

      If you really care about compatibility, you can define a set of options that can be activated with a special variable or flag, some kind of a compatibility level.

      Update: consider an analogy: if you had the choice between two programming languages, one being perl, and the other being strictperl, that is perl with use strict; use warnings; enabled by default - which one would you use? I'd stick with perl for my old scripts that aren't strict safe, and chose the strictperl version for everything that I newly write.

        I'd stick with perl for my old scripts that aren't strict safe, and chose the strictperl version for everything that I newly write.

        Good news: strictperl exists (though it only enables strict; warnings are more problematic conceptually.)

        Bad news: it's a patch against bleadperl and much of the Perl core does not run with strict enabled unilaterally.

      I agree fully with this node. I love Postgres, and it is most certainly better than Inno for many reasons. But, these stupid posts fail to acknowledge anything MySQL has done right and instead they cherry pick the most-wrong way to do something and present it in such a biased fashion that Bill O'reilly would cry blood. If you want to compare apples with apples, compare the *newest* version of Postgres (8.3) with the *newest* version of MySQL and ignore the non-inno backends. It does surprisingly well.
      Pg still has so much room for improvement, and it certainly isn't the easiest of the two to develop in. Though I would have to say, I've seen MySQLs "user variables" abused to do some pretty simple tasks that can be done with a simple Pg aggregate.


      Evan Carroll
      I hack for the ladies.
      www.EvanCarroll.com

        If you want to compare apples with apples, compare the *newest* version of Postgres (8.3) with the *newest* version of MySQL

        Cool idea. Why not get to work on it? Myself, I'd love to spend the next year working on database benchmarks and reliability tests just so I can have an informed opinion in a geek debate. Of course, no one will actually pay any attention to you, and by the time you're done the databases you're studying will have changed some more, but what the hell.

        and ignore the non-inno backends.

        You mean, pretend that most people aren't using the default?

        It does surprisingly well.

        Ah, so you do have some data? You've compared the results with both databases, then? What was surprising about your results?

        Pg still has so much room for improvement, and it certainly isn't the easiest of the two to develop in.

        And where would that "room for improvement" be, precisely? I can't imagine why you'd find it harder to develop for than any other database.

        (Department of cheap irony: perlmonks.org hit a "database error" when I first tried to post this.)

      One problem with the "strict mode" solution is it's set on the client. It means you have to be careful about what clients access the DB, or one misbehaving tool could ruin it for everyone. Altho the improvements are what we've all been asking for, they kind of feel "bolted on", whereas they were fundamental design goals of Postgres from the beginning.

      Ultimately, both are good choices though. Open source is about giving us more choice, not less. I think the only thing is some people see the success of MySQL as disproportionate to it's quality, when compared to Postgres. (But maybe it's just an example of "worse is better").

        It can be changed on the client, but the default is set on the server. Someone would have to intentionally change it in order to have this problem. If a malicious user has access to your database, changing the SQL mode is the least of your worries.
Re: [OT] Why I don't use Mysql for new projects
by dragonchild (Archbishop) on Jul 10, 2008 at 15:31 UTC
    I've been both a MySQL DBA and an Oracle DBA. I've also hung out with Oracle DBAs who've been doing it since Oracle v4. Oracle, the gold standard by which all other RDBMS engines are measured, is accounted to be the absolutely worst RDBMS engine that could possibly have ever been written. Except, of course, for all the others.

    Data management is a particularly tough problem. Particularly if the following requirements are all accounted critical:

    • Reliability - it never crashes
    • Durability - when it does crash, nothing is lost
    • Speed of queries
    • Speed of mutations
    • Concurrent access by thousands of simultaneous connections
    • Scalability across multiple CPUs and multiple machines
    • Ease of use
    • Ease of administration

    Maintaining DBM::Deep has given me a very small window into what the developers of various RDBMS engines must go through in order to achieve those criteria. It's not surprising that each RDBMS has chosen to short some of those requirements in order to maximize others. Oracle, for example, has horrible ease of use, ease of administration, and speed of mutations isn't that great. And, frankly, Oracle has had horrible failures in terms of reliability. For example, in 9.2.0.2, there was a bug in how TIMESTAMP columns were stored. When it was found, the only solution was to upgrade to 9.2.0.4 and accept that there was lost data.

    The difference between MySQL and Oracle? MySQL has put ease of administration much higher on the list. You would never consider using Oracle, even if you could use it for free. But, why on earth do you think that you can just willy-nilly use MySQL or PostgreSQL? Being a DBA is flipping hard. There's a gazillion things to consider, most of which you never even realize until you were bit in the ass by them.

    So, don't blame MySQL for your lack of understanding of its architecture. There are courses for a reason. If you're going to entrust your production-critical data to something, why wouldn't you spend the week to really read and understand the documentation, regardless of the systems you choose to use? Or, are you going to tell me that you don't really understand Perl, Linux, or Apache? Are you just blithely assuming that people who aren't paid have your best corporate interests in mind?


    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
      So, don't blame MySQL for your lack of understanding of its architecture.

      I didn't blame MySQL for anything. I just told everybody who cares to read why I don't use it for new projects. That's a bit of a difference.

      If you want to, you can read that as "moritz doesn't use MySQL anymore because he doesn't understand its architecture".

        I'm reading your concerns and your response to perrin as "he doesn't understand its architecture."

        Now, you have very valid points with regards to MySQL's suitability for certain tasks. Oracle is still the gold standard for a reason (and it's not its pricepoint!). That said, MySQL is suitable for most tasks, assuming you set certain defaults appropriately. I don't ever do anything unless it's in InnoDB or NDB (if I need clustering). I always use the strictest sql mode and I set all my charsets to Unicode or Latin-1 (depending). There are certain features I will never use and certain features I don't consider optional. Same as my thoughts on Oracle, Sybase, and DB2. Oracle's defaults suck horribly. There isn't a single Oracle DBA that would ever consider using them. Does that make Oracle a bad RDBMS?


        My criteria for good software:
        1. Does it work?
        2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
Re: [OT] Why I don't use Mysql for new projects
by nikosv (Hermit) on Jul 10, 2008 at 16:37 UTC
    When looking for an open source database give a try to Ingres.
    It's the very first RDBMS product, very mature and reliable. It has a fine transaction engine,runs on many platforms, has many connectivity options such as native .NET data provider , DBI etc
    and has many features such as writing ESQL/C programs, ABF/4GL and Openroad a tool for rapid development which can also be used to bring rich applications on the web.
    It's left behind marketing wise because as a company they had structuring problems but now they released it on open source.
    I've been using II 2.6 on Unix for 5+ years now and although the version I use is old and lacks features I still find it a solid product.But the new Ingres 2006 is a different proposition all together.
      Ingres is old, and openroad was definitely garbage in terms of GUI. Since you mentioned, I checked out their web site, openroad looks a bit different now. I may download and play with it a bit. The 4GL thing didn't really work out, and is no longer a topic.
        The 4GL thing didn't really work out, and is no longer a topic.
        Can you be more specific?I don't think that this is true.There are millions of programs and robust applications made by using ABF/4GL code during the 90's and are still being developed/maintained.
        Furthermore the language that Openroad uses is 4GL with object oriented extensions.You can literally copy and paste 'old' procedural ABF code that works in Unix into Openroad and with minor changes in declarations here and there you can have a fully blown GUI application,thus to use Openroad you have to know 4GL.So I don't see where it did not work out.
Re: [OT] Why I don't use Mysql for new projects
by psini (Deacon) on Jul 10, 2008 at 20:03 UTC

    Disclaimer: I never used MySQL for I'd been introduced to RDBMS using PostgreSQL.

    This said, I read this thread and found mainly defensive post on the line of "MySQL is not that bad".

    I, as a non MySQL user, would like to hear some answers to another question: why is MySQL better than PostgreSQL? Or, better, are there applications/environments in which MySQL is preferable to PostgreSQL? And why?

    Please, don't consider this as a rhetorical question, I'm really looking for answers. By now the only advantages I know of are the wider availability of MySQL (at least in the web hosting providers world) and (I've been told, no personal experience) a performance gain when handling small sized DB's.

    Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."

      I, as a non MySQL user, would like to hear some answers to another question: why is MySQL better than PostgreSQL? Or, better, are there applications/environments in which MySQL is preferable to PostgreSQL? And why?

      I'm by no means an expert either, but this is what I read multiple times:

      When you have data this doesn't change very often, and you make many simple queries, MySQL (with MyISAM backend) is blazingly fast.

        If those are the only advantages, then MySQL is a waste of effort. If you rarely have insertions, deletions, or updates and you have a high volume of simple queries then OpenLDAP will not only have MySQL's lunch but will hand Postgres the empty tray and smack Oracle on the behind as it whistles and dances out of the cafeteria.
      The problem is that very few people have actually used both MySQL and Pg in major performance-sensitive applications, so it's hard to get a good comparison. I have used Pg, but not as much as I've used Oracle and MySQL.

      I can tell you a few things I like about MySQL, even though I can't really compare them to Pg in a meaningful way. I like how easy it is to administer. I like how fast the InnoDB storage engine is (faster than MyISAM for most of the work I do, even with transactions and foreign keys). I like the excellent documentation.

      And finally, I like the fact that I know other people have done very serious work with it and succeeded. Yahoo, Google, Craig's List, LiveJournal... all of these companies use MySQL, and handle much more data than I need to. That gives me confidence that it can handle my work.

        There are certainly some big mysql success stories, but they tend to be in applications that aren't just that fussy about things like data integrity. Google is a case in point: they're doing really well with replicated myisam tables, but if something weird happened to the data no one is likely to even notice. It's not like, say, handling banking transactions.

      Something I like about PS over MS is its sequence support. At the same time I like MS better in some ways for the same issue because it has auto_increment instead and it is almost all I ever need and it would be a drag to have to use an extra table (not sure it's a full fledged table, but you get the idea) every time. The same goes for the data type ENUM (not sure if PS has SET).

        At the same time I like MS better in some ways for the same issue because it has auto_increment instead and it is almost all I ever need and it would be a drag to have to use an extra table (not sure it's a full fledged table, but you get the idea) every time.

        It's not any kind of a table, it's a sequence, which consumes only a handful of bytes, pretty much exactly like auto_increment does. The big advantage of a sequence over auto_increment is that if you need to you can use more than one sequence in the same table, or share a sequence between tables, which you can't do with MySQL (or couldn't do the last time I looked at it anyway)...

        The same goes for the data type ENUM (not sure if PS has SET).

        Not sure what you mean by the ENUM, since PostgreSQL does indeed have ENUM, just not in the backwards column-level way MySQL does it, which forces you to redefine the enum in every column you want to use it, even if it should be the same for all those columns.

        # MySQL CREATE TABLE color_choices ( foreground ENUM ( 'RED', 'GREEN', 'BLUE' ), background ENUM ( 'RED', 'GREEN', 'BLUE' ) ); # PostgreSQL CREATE TYPE colors AS ENUM ( 'RED', 'GREEN', 'BLUE' ); CREATE TABLE color_choices ( foreground colors, background colors );

        And while PostgreSQL doesn't have SET, it does support array data types, which get you pretty much the same end result, with less work.

        # MySQL CREATE TABLE color_choices ( favorites SET ( 'RED', 'GREEN', 'BLUE' ), least_favorites SET ( 'RED', 'GREEN', 'BLUE' ) ); # PostgreSQL CREATE TYPE colors AS ENUM ( 'RED', 'GREEN', 'BLUE' ); CREATE TABLE color_choices ( favorites colors[], least_favorites colors[] );

        Also, if you like SET, you should be sure and read The MySQL SET Data Type, especially the section entitled "Why You Shouldn't Use SET".


        www.jasonkohles.com
        We're not surrounded, we're in a target-rich environment!

        Nice rundown, thanks. This was meant for jasonk, of course. So sleepy...

Re: [OT] Why I don't use Mysql for new projects
by Your Mother (Canon) on Jul 11, 2008 at 03:52 UTC
    Last I looked, foreign key constraints didn't work. This seems to be fixed now innodb, but requires migration to innodb.

    Last I used Postgres it had a nasty memory leak that crashed the production DB at work at least once a day. I believe it was fixed several years ago though, which I also believe was a couple of years after InnoDB and foreign keys made it into MySQL, so I don't go around telling people that Postgres leaks. :)

    I agree that many of the MySQL defaults are annoying and end up as hard lessons sometimes, but I also agree with dragonchild that DB administration is not easy and without reading the documents thoroughly bad things are likely to happen with just about any software.

Re: [OT] Why I don't use Mysql for new projects
by Pic (Scribe) on Jul 12, 2008 at 13:56 UTC

    There's one thing you didn't mention that annoys me with MySQL: it ignores CHECK constraints. It parses CHECK constraints, but it ignores them (on all backends, according to the manual for 6.0).

    And it's this kind of stuff that truly annoys me the most with MySQL: It will look like something worked like you'd expect, but in fact MySQL has silently ignored something, or done something else. There used to be a page on this, but it seems to be gone at the moment (Google has a cached copy here).

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://696677]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (11)
As of 2014-12-28 23:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (183 votes), past polls