MySQL sucks

(Warning: too much wine makes Andrew rant.)

It seems I was a bit too charitable about MySQL back when I saidI’m firmly convinced PostgreSQL is the better database”.

What I should of actually said is “MySQL is a worthless piece of shit that doesn’t deserve to be dignified by the moniker ‘database’ and should be eradicated from this earth“.

An example:
mysql> drop database foobar;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop database foobar;
Query OK, 0 rows affected (0.01 sec)

The explanation is in the user comments for the DROP DATABASE documentation:

DROP DATABASE seemingly drops each table, first, in arbitrary order. Since the ordering is arbitrary, it may drop them in the ‘wrong’ order. In this case, you’ll get the message … and half of your tables will be gone, the other half remaining. You can repeatedly execute “DROP DATABASE” commands and each time (at least for me), MySQL gets closer to an empty database, at which point, the database itself is dropped.

The bug report and subsequent thread have an official response from the MySQL developers … basically, foreign keys could link to other databases. So why not check for links to foreign databases, and only screw up in those cases?

This is yet another example of the difference between a proper database (Postgres, Oracle, DB/2, etc) and a pile of scrappy code that has gained prominence through canny marketing and a few high-profile sites using it because they don’t know any better.

Sorry, Jeremy, but I don’t believe it comes down to a misunderstanding of how the database works, or of unfair comparisons with the competition. The example above is just one of the many ways in which MySQL is half-arsed, obtuse, and dangerously broken.

*sigh*

Currently listening to Aerodynamic from the album Discovery by Daft Punk

This entry was posted in Computing. Bookmark the permalink.

9 Responses to MySQL sucks

  1. Adrian Bool says:

    In MySQL’s defence we’ve used it for many years where it served as an extremely reliable, and above all fast database. In our context, we only needed the most basic of functionality, only a little beyond gdbm.

  2. Alex Hudson says:

    I will come to MySQL’s defence also ;) Firstly, I think MySQL is good in that it enforces the designer to think about how they’re setting up their database – too many times have I heard someone moan about how MySQL is not powerful enough for their needs, when actually what they’re doing is attempting to impose some crack-addled database design using bizarre constructs only supported in the big boys. The imposed simplification is definitely a win in my book.

    Also, Postgres has some really ugly features. Its security system is, frankly, horrible, and the complexity of some stuff (handling of auto incrementing fields, for example, esp. wrt. script-imported databases) makes me want to pull my teeth out.

    In many ways, though, I think it’s just what’s more comfortable. I haven’t seen a job that absolutely required Postgres for quite some time, and as MySQL improves I expect not to. But, it’s what you’re used to that counts. Personally, I run both, but for commercial work stick solely to MySQL. It’s just better supported.

  3. Why can’t people leave me to moan in peace? ;-)

    Aid: sure, I seem to remember you using MySQL for things like logging – where arguably you don’t need a “proper” database. This is perhaps what MySQL should be relegated to?

    Alex: I don’t quite see how MySQL enforces the designer to think about how they are setting the database up – at least, no more than any of the other databases. I’ve seen some crack-addled database design in MySQL databases, and I’ve seen some in PostgreSQL too. Ultimately, if you have no clue, you’re gonna screw the database up no matter what RDBMS you’re using.

    I’m not sure what you don’t like about PostgreSQL’s security. MySQL makes it convenient by storing it in database tables, which Postgres can immitate. But Postgres can also work with pluggable authentication, or kerberos, or crypt, or ident, etc. It’s all about choice.

    I’ve never had a problem with auto-incrementing fields – they seem to be much the same in MySQL or Postgres – except in MySQL you explicitly state “auto_increment”, where Postgres has a column type “serial”. See http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html and http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-SERIAL .

    Finally – care to clarify “better supported” for MySQL? Do you mean via mailing lists, or commercial support? I’m not aware of MySQL’s developers being particularly more active than the Postgres ones. Mind you, with Postgres I’ve never even needed support ;-)

  4. Alex Hudson says:

    MySQL is definitely easier to setup and administer – the Postgres host-based auth stuff is particularly annoying. Yes, Postgres can imitate it, but it’s effort and hassle. Ditto auto_increment – Postgres requires you to know about sequences, it doesn’t handle it for you. If you don’t realise that, for example, you’ll find you cannot use a database after a data import. Go look on the user lists for the people who continue to have issues with it.

    Better supported – I actually mean installed base here, sorry. More likely to come across it installed, easier to procure, etc. And I don’t think that’s a popularity thing necessarily (although the PHP compatibility thing is obvious a big factor in the web host arena), MySQL in my experience is less hassle to administer.

    But by all means, continue to rant, it’s entertaining, your website after all.

  5. Alex Hudson says:

    Btw, which Aerodynamic is better – Daft Punk, or Kraftwerk?

  6. Not heard the Kraftwerk one, that I know of. So Daft Punk, obviously!

    Gonna leave the MySQL arguments now – since it’ll descend into personal preference very rapidly. Though I still think you’re overstating the difficulty of sequences ;-)

  7. aid says:

    Savs, Not just for logging. Although the official copy off all our user data was in an Oracle DB all servers actually queried MySQL databases for RADIUS Auth, SMTP routing, FTP access – the lot!

    Actually, strangely enough RADIUS accounting was done on PostgreSQL.

    It’s quite a while ago go when we started using MySQL, and back then it only had the most basic SQL support and so could not have been say our main database – but it was bloody fast – and that was all that mattered to us for our effectively ‘cache’ databases to take load of the (very expensive) Oracle DB.

  8. Alex Hudson says:

    Oooo, go listen to the Kraftwerk version. It’s on their current album, I think.

    BTW, if you think MySQL is bad, I’m sure you’re going to hate the SQLite system being built into PHP et al at the moment ;)

  9. experience says:

    MySQL had better promotion, and it works on win32, which made its broad acceptance and use. But it lacks real database features, any DBA will agree with that.
    I think it *is* dangerous to let people call MySQL stuff a “database”. This word should be only used for software implementing a database. PostgreSQL does. It’s fast, secure, simple, reliable, you don’t even need support ! … PostgreSQL highly recommended.