Why MySQL is not my favourite database.

[reddragdiva]

From a Hacker News thread on MongoDB. Someone asks "why use MongoDB?" I answer:

Same reason as MySQL: it's there, it's popular, it's the first thing that springs to mind. That it's shit doesn't factor in.

proveanegative:

Arguably, MySQL/MariaDB has over the course of its life improved to the point where it is a reasonable product. I normally wouldn't pick it over PostgreSQL for a new project but it doesn't easily lose data any more, either, and it has good read performance with the default backend.

davidgerard:

It's not an unmitigated disaster, true. But speaking as a sysadmin whose problem it is, it's still horrible to administer for something that turns out to be business-critical, and it pains me whenever a third-party useful thing pretty much requires it. (Typically PHP stuff where the paid developers hyperoptimise for MySQL, and there's hypothetical PG support which is actually half a volunteer.)

proveanegative:

>Typically PHP stuff where the paid developers hyperoptimise for MySQL

That's quite true. My experience with MySQL comes from working with company intranet installations that didn't see that much traffic and some web apps, all with a high read-to-write request ratio. They all ran off a single DB server (some hardware, some VPS), so my administrative work was limited to automating fairly straightforward tasks with things like Ansible. I'm curious to hear an example of the kinds of problems you've run into with MySQL, since I assume you deal with more complex and larger-scale deployments. (And I'm looking for anecdotes to help persuade customers and developers alike to give Postgres a try.)

davidgerard

Nothing hugely complicated. Production Magento, Drupal and WordPress - we managed to outsource the WordPress, so now it's mainly now Magento, and that's much more horrible in itself than MySQL - one in-house tool that used to use it, some MediaWiki, and one really badly-behaved in-house tool that picked MySQL without asking us first.

Mostly we were bitten by long-running MySQL sillinesses:

• its strange idea of UTF-8 (we call the MySQL version WTF-8 - see http://geoff.greer.fm/2012/08/12/character-encoding-bugs-are-%F0%9D%92%9Cwesome/ )
• InnoDB's galloping disk consumption
• the Debian/Ubuntu package's default stupid behaviour of putting all the InnoDB databases into a single file ibdata1 (I hope this is a Debianism and not something that's default in upstream)
• ibdata1 never shrinking ever (bug #1341, open since 2003)
• binary replication issues (e.g. bug #68892, which is fixed but that doesn't help older or distro versions)
• several others I've mercifully obliterated the braincells that were holding them. But all of these were long-known issues that will never be fixed for one reason or another (backward compatibility with past mistakes, or they just can't be bothered).

Here's a good crib: http://grimoire.ca/mysql/choose-something-else

tl;dr MySQL: the Comic Sans of databases. Except Comic Sans has use cases.



If you have someone attempting to use MySQL internally, pick and choose from those issues and some from that "good crib" link.

Update: Apparently the single file for all InnoDB was standard upstream until 5.5. "This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes." YES, BUT YOU NEVER GIVE IT BACK, SO THAT'S NOT ACTUALLY BLOODY USEFUL, IS IT NOW.

Update May 2015: Why MySQL is probably not WordPress's favourite database either. MySQL does in fact have a strict mode; you should be so lucky as to have your third-party software support it. (WordPress are working on this.)

Comments

[hellsop]

MySQL is a perfect product for anything you don't care about in the first place, and just want to be quit of soon. If I have to take care of it in the future, we're going DB2 Express or Enterprise, depending on whether there's budget or not.

[reddragdiva]

We're shifting our own stuff from Oracle to Postgres. We have one piece of vendorware that relies on Oracle, but we're getting rid of that.

[tcpip]

Yeah, pretty much all this. Whenever possible for anything considered important I prefer Postgres. MySQL tends to get a jersey primarily through general ubiquity, familiarity, and laziness (including my own).

[reddragdiva]

MySQL is fatally easy to use and administer. It's the bit where you want your data back that gets a bit problematic.

Use cases and basket cases...

[hairyears]

And the real-world use-case is an enforced choice between "IT won't let us use a DB server" and "So we built a Microsoft Access data back-end".

Re: Use cases and basket cases...

[reddragdiva]

I thought it was "So we put it in a spreadsheet". "And we wrote the logic in the spreadsheet too."

[vatine]

Nah, with proper backups it's not even a problem. Admittedly, we did run into "mysql doesn't scale no more" and made Spanner, which does.

Nervous tac

[hairyears]

Tactical development has two rules:

1. However bad it is, consider the possibility that it is a replacement or a substitute for something even worse;
2. It is always possible to *make* it worse.

[reddragdiva]

More generally, if you account for MySQL's myriad stupidities and gotchas, you will get your data back. Much as you can write good code in PHP if you avoid its myriad stupidities and gotchas. That doesn't make either any good.

[vatine]

That is indeed the case.

That's not a big, it's a feature!

[(Anonymous)]

One of my biggest issues with MySQL was not so much technical as political, in that they had a habit of spinning missing features as being somehow advantageous or non-issues when it suited them. In other words, when asked about why MySQL didn't have certain features (subqueries, transactions, etc.), instead of saying "we're working on it" or "it's not a priority for us", they'd present some self-serving "white paper" arguing why that feature was bad and you should feel bad for wanting it. Their arguments against transactions were particularly hilarious. Of course, once InnoDb came on the scene, having transactions was suddenly important after all.

Re: That's not a big, it's a feature!

[reddragdiva]

Yes, and NoSQL (particularly Mongo) doing the same.