The loader had run every night for six years: a Perl script that slurped a vendor CSV and INSERTed about 300,000 rows into a reporting table. It survived MariaDB 10.0, 10.1, 10.3, and 10.5 without a single code change. The morning after the 10.6 upgrade it was dead at 03:14 with ERROR 1366 (22007): Incorrect integer value: '' for column 'units' at row 4182. Row 4182 had an empty units cell — as had thousands of rows every night for six years. The data had not changed. The vendor file had not changed. What had changed, four releases earlier in MariaDB 10.2.4, was the default sql_mode: STRICT_TRANS_TABLES had joined the default set, and every box that upgraded stepwise had produced warnings people ignored, while this one jumped far enough that the first symptom was a hard failure. An empty string into an INT column went from "coerced to 0 with a warning nobody reads" to "statement rejected, transaction rolled back, loader dead."
sql_mode is one of those settings everyone inherits and nobody curates, right up to the upgrade where it starts enforcing things. This is the field guide I wrote for our team after that week: what the default actually contains, why zero dates are the quietest data corruption you will ever ship, what MariaDB deliberately does differently from MySQL on GROUP BY, and how to tighten the mode on a running fleet without breaking legacy jobs.
What does the default sql_mode actually contain?
Since MariaDB 10.2.4 the default is STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION — strict on data integrity for transactional tables, silent on everything else. Two facts in that sentence carry operational weight. First, the strictness arrived as a default change, not a behavior flag day: any config file that sets sql_mode explicitly kept its old value across upgrades, and any server installed fresh after 10.2.4 got the strict default, so a mixed fleet can have two "identical" servers enforcing different rules. Check the actual value, not the assumed one:
-- the truth, global and for your current session
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
-- is anything running with a non-default session mode?
SELECT ID, USER, HOST, DB, TIME, STATE
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 3600;Second, sql_mode is layered: the global value seeds new connections, and every session can override its own. That layering is the escape hatch that saved our loader — SET SESSION sql_mode = '' at the top of the legacy script restored the old coercion behavior in five minutes, buying us a quarter to fix the CSV parser properly. Treat session-level overrides as documented, ticketed debt, though: they are invisible to anyone auditing the global config, and the next person to "clean up" the script inherits a mystery.
How do zero dates corrupt data without anyone noticing?
MariaDB accepts the zero date '0000-00-00' into DATE and DATETIME columns by default, and silently converts invalid dates like '2026-02-30' into it when strict modes are off — no error, and often no warning anyone captures. The corruption is silent because the insert succeeds; it surfaces months later as weird analytics. Our legacy events table had 1.4% of rows — 2.1 million of them — sitting on event_date='0000-00-00', contributed by years of a PHP form that passed empty strings through. Every query doing date arithmetic on those rows got NULL, every monthly aggregation quietly dropped them, and one downstream revenue report had been 1% low for what I can only assume was years.
The enforcement modes are NO_ZERO_DATE and NO_ZERO_IN_DATE, and they are deliberately not in MariaDB's default set — STRICT_TRANS_TABLES alone only rejects zero dates in a narrower set of cases, and ALLOW_INVALID_DATES exists to explicitly permit storing impossible dates like February 30th as-is. If you want the full behavior of the strictest common setups, the composite TRADITIONAL mode bundles STRICT_TRANS_TABLES with NO_ZERO_DATE, NO_ZERO_IN_DATE, ERROR_FOR_DIVISION_BY_ZERO, and friends. The production-safe rollout for a table with existing zero dates is three steps: find them (SELECT COUNT(*) WHERE event_date = '0000-00-00' — note that comparing with the string works and is the honest way to count them), decide what they mean and backfill or quarantine, then enable the modes. Enabling first and discovering second is how you get a 03:14 incident of your own.
Why doesn't MariaDB force ONLY_FULL_GROUP_BY like MySQL does?
ONLY_FULL_GROUP_BY exists in MariaDB but has never been part of its default sql_mode — a deliberate divergence from MySQL, which made it default in 5.7 — and that single difference explains the most common "works on MariaDB, breaks on MySQL" query class I see in migrations. With the mode off, MariaDB lets you SELECT columns that are neither grouped nor aggregated, and it returns a value from an arbitrary row in each group. With it on, the same query dies with ERROR 1055 (42000): 'reporting.orders.region' isn't in GROUP BY.
Two nuances separate people who have hit this from people who have read about it. MariaDB does functional-dependency detection: if you GROUP BY a primary key, selecting other columns from that same table is legal even under ONLY_FULL_GROUP_BY, because the values are determined. So the mode punishes genuinely ambiguous queries, not all loose ones — the failures cluster on joins and groupings by non-key columns. And the fix for "any value is fine, I just need one" is to say so with MIN() or MAX() around the column; do not go looking for MySQL's ANY_VALUE() function, because MariaDB does not have it — that is a MySQL-only invention, and cargo-culting it into a MariaDB migration produces a fresh syntax error. When we enabled ONLY_FULL_GROUP_BY in staging for a new service, 11 of 240 legacy report queries failed; nine were honest bugs returning arbitrary rows, two were fine and got explicit MIN() wrappers. That ratio — mostly real bugs — is why I enable it everywhere except pinned legacy sessions.
How do you tighten sql_mode on a fleet without breaking legacy jobs?
Change it per session for the jobs that need the old behavior, change the global default in the config file for everything new, and never flip a production global mid-traffic without a rehearsal. The working sequence: inventory first — grep the codebase and cron repo for SET sql_mode, SET SESSION sql_mode, and TRADITIONAL, because jobs that already pin their session mode are immune and jobs that do not will inherit whatever you set. Then set the target mode in my.cnf under [mariadb] so it survives restarts, roll it to one replica, and run the full application test suite plus a day of shadow traffic against it. Only then promote the change. Legacy loaders get an explicit SET SESSION sql_mode at connection time with a comment naming the ticket that will remove it.
Two replication-era gotchas complete the picture. Session sql_mode travels with the statement in the binary log, so a writer running a lax session mode produces binlog events a strict replica applies under the writer's mode — your replica does not rescue data the primary was lenient about, which matters for the failover-pair consistency I covered in the multi-source replication notes. And dumps carry their own opinion: mariadb-dump writes a SET SESSION sql_mode near the top of every dump file, so a restore can succeed with coercions that the live server would reject — a restored staging box is not proof the data is strict-mode clean. If you are planning an upgrade anyway, fold the sql_mode audit into the rehearsal pass from the 10.6-to-11.4 upgrade field notes; the two share a staging environment and a week. Teams coming from MySQL should also read the divergence catalog in migrating from MySQL: gotchas — the GROUP BY default is only the loudest of several behavior gaps.
Where MonPG fits
The signals worth trending around sql_mode are indirect but reliable: spikes in error 1366/1292/1055 rates after upgrades or deploys, counts of zero-date rows on tables that feed revenue reports, and the inventory of session-level overrides so the debt list shrinks over time instead of growing. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and error-rate and data-quality signals like these are on the list of things it is being built around. Until that ships, the queries above and a quarterly audit are your kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.