Technology... The Madness of MySQL

Back working for a start-up again.  This brings all the pluses and minuses as per usual.  Crazy hours sometimes, fun projects, more control, less process.

It also brings something else.  MySQL.

Anyone who knows me, knows just how much visceral hate I have for this "database".  In the last 48 hours, I've learnt two more fun facts to add to the list of solid reasons why to skip MySQL in favor of better solutions:

1. Nullable false leads to an implicit default.

We use Liquibase to version our database.  It's good.  But, it means that generally I find myself writing schema in XML format, not in SQL format.  Creating a new column on a table is easy enough, and there's even a handy XML block for constraints.  I copied the definition from another column definition from before, which included a nullable=false constraint, which on first glance, seemed appropriate enough.

I run the update, and wonder what's taking so long...

Turns out if you specify nullable="false", MySQL will use a default value, all databases would, that's perfectly sensible.  The thing that's not sensible is is that in this case, I didn't specify one!  So MySQL, instead of throwing an error, telling my schema change is invalid and missing something, just goes ahead and implies a default.  Stop implying things MySQL, you're guessing what I mean, not doing what I say.  This is generally a bad thing for software to do.  And it implied I meant that I did in fact want a default value and that default value should be 0 for a bigint column.  Not a bad assumption necessarily, but, an assumption nonetheless; and to assume, makes an ass out of u and me, but in this case, mostly just me.  This of course then expended a lot of CPU resources to apply as this particular table has a great many rows.  No problem, kill the session and remove the constraint.

Which leads us swiftly to number 2...

2. Adding a column to a table in MySQL requires a full table update, even in InnoDB.

o_o. o_O.  O_O.

I think in 2018, every single other database engine handles this correctly.  It's a meta-data change.  Not so in MySQL.  Even with no default value, the database engine insists on rebuilding the entire database store.

So if you're an enterprise with a large(ish) table, and you need a new column.  Downtime will be required.  Downtime in 2018 is not what users have come to expect.  Downtime is never acceptable to users.

MySQL.  I didn't think I would discover new reasons to despise you for being a pile of poorly implemented not really ACID compliant unhelpful non-SQL standard compliant database.  I was unpleasantly surprised.