STRICT_ALL_TABLES now default in MariaDB


Some tables joining. HAHA! Hah! Hah.

I’ve used MySQL and its offshoots in tandem with Postgres for most of my career and for personal projects, but with the fairly large caveat that it’s running in this mode, defined in my.cnf:

sql-mode = "STRICT_ALL_TABLES"

From the MariaDB docs:

Strict mode. Statements with invalid or missing data are aborted and rolled back. For a non-transactional storage engine with a statement affecting multiple rows, this may mean a partial insert or update if the error is found in a row beyond the first.

I never had the data loss or issues that Postgres friends warned me about, even with large-scale deployments. But I will readily admit that not accepting this strict mode by default made me nervous. Enabling it never made difference in operation, so why not?

(That’s not strictly speaking true, I trialled a few self-hosted blog platforms and wikis back in the day that stopped having problems when I turned this off. But that told me that I shouldn’t be using them).

I noticed recently though that this has changed:

With strict mode set (default from MariaDB 10.2.4), statements that modify tables (either transactional for STRICT_TRANS_TABLES or all for STRICT_ALL_TABLES) will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

This is great news, and I’m only a few years late celebrating. The Tyranny of the Default means people weren’t running in this mode before. Being default means more people will be.

Author bio and support


Ruben Schade is a technical writer and IaaS engineer in Sydney, Australia who refers to himself in the third person in bios. Wait, not BIOS… my brain should be EFI by now.

The site is powered by Hugo, FreeBSD, and OpenZFS on OrionVM, everyone’s favourite cloud infrastructure provider.

If you found this post helpful or entertaining, you can shout me a coffee or buy some silly merch. Thanks!