Why MySQL is not my favourite database.

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.)