Set MariaDB SQL Modes Without Breaking Applications

MariaDB SQL mode changes how the server parses SQL, validates values, handles dates, evaluates grouped queries, treats quotation marks, and emulates other database dialects. It can turn silent truncation into an error, expose ambiguous reports, or make a previously accepted stored-routine definition fail to parse. That makes sql_mode a valuable correctness control and a serious compatibility boundary.

The dangerous approaches are to copy a mode string from another database product, enable it globally without testing, or disable strict behavior whenever an application reports an error. A strict-mode failure often identifies a real bug or invalid historical data. Hiding the failure may let corruption continue.

This guide inventories global, session, account, application, and stored-object behavior; explains high-impact modes; builds a compatibility test suite; cleans existing data; stages changes by workload; monitors rollout; and defines rollback without abandoning data quality.

Understand global and session scope

MariaDB exposes both global and session sql_mode:

SELECT @@GLOBAL.sql_mode AS global_mode,
       @@SESSION.sql_mode AS session_mode;

When a client creates a connection, its session normally inherits the current global value. Later SET GLOBAL changes affect new connections, not sessions already open in application pools.

Change only the current connection:

SET SESSION sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Test a new global default at runtime:

SET GLOBAL sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Runtime global changes do not normally survive restart. Persist the approved value in an option file. Also open a new connection for verification; the session that executed SET GLOBAL may still show its old session value.

Applications can execute SET SESSION sql_mode after connecting. Frameworks, migration tools, proxies, and dump files may do this without an operator noticing. Therefore, a server global value is not proof of application behavior.

Inventory every source of SQL mode

Capture server version and runtime modes:

SELECT VERSION(), @@hostname, @@port;
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@GLOBAL.old_mode;

Inspect option files and parsed defaults:

sudo rg -n '^[[:space:]]*(sql[-_]mode|old[-_]mode)[[:space:]]*=' /etc/mysql
my_print_defaults server mysqld mariadb mariadbd | 
  grep -E 'sql[-_]mode|old[-_]mode'
mariadbd --print-defaults | tr ' ' 'n' | 
  grep -E 'sql[-_]mode|old[-_]mode'

Search application and deployment configuration:

rg -n -i 
  'sql_mode|sql-mode|SET[[:space:]]+(SESSION[[:space:]]+)?sql_mode' 
  /srv/apps /etc/systemd/system /opt/deploy 2>/dev/null

Do not print secret-bearing configuration broadly. Restrict the search scope and sanitize results.

Inventory connection pools separately. A migration process may use a strict session mode while runtime traffic inherits the server default. That can be intentional, but it must be documented and tested.

Know the default but do not depend on it

MariaDB's documented default from 10.2.4 includes:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Defaults and valid modes can differ by release, package, compatibility settings, and future changes. NO_AUTO_CREATE_USER is retained in the documented default for MariaDB even though account creation should use explicit CREATE USER in modern operations.

An upgrade can expose code that depended on an old default. Specify required behavior explicitly and include sql_mode in upgrade tests. Do not assume MariaDB and MySQL with similar version numbers share the same default or semantics.

Understand strict mode

MariaDB is in strict mode when either STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.

STRICT_TRANS_TABLES rejects invalid changes for transactional tables such as InnoDB. Behavior for nontransactional engines can differ, especially when an error occurs after part of a multi-row statement has already changed data.

STRICT_ALL_TABLES applies strict validation to all storage engines. It can be preferable when the estate is controlled, but partial-change implications on nontransactional tables still require testing because those engines cannot roll back like InnoDB.

Without strict mode, MariaDB can adjust invalid input and emit a warning: truncate an overlong string, clamp an out-of-range number, or transform an invalid value. Applications often ignore warnings, turning a visible error into silent data loss.

Test with a disposable table:

CREATE DATABASE sql_mode_lab;
CREATE TABLE sql_mode_lab.validation_test (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  short_text VARCHAR(5) NOT NULL,
  quantity TINYINT UNSIGNED NOT NULL,
  event_date DATE NOT NULL
) ENGINE=InnoDB;

In a controlled session, set the candidate mode and test invalid cases:

SET SESSION sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

INSERT INTO sql_mode_lab.validation_test
  (short_text, quantity, event_date)
VALUES
  ('too-long', 300, '2026-02-30');

Record the real error from your server; do not invent output. Then verify no unintended row exists and inspect warnings when testing a non-strict control session:

SHOW WARNINGS;
SELECT * FROM sql_mode_lab.validation_test;

Use separate disposable sessions because session modes persist until changed or disconnected.

Prefer fixing data over disabling strict mode

When strict mode breaks a write:

Capture the statement class and sanitized parameter types.
Inspect the target schema.
Determine whether input violates an intended business constraint.
Fix validation, column type, or transformation.
Find existing rows created under permissive behavior.
Add regression tests.
Keep strict mode unless a reviewed compatibility reason remains.

Examples:

A VARCHAR(20) receives 35 characters: expand the column if the business value is valid, or reject it before SQL.
An unsigned quantity receives -1: fix application semantics rather than clamping to zero.
A date parser emits 0000-00-00: use NULL for unknown values or reject the record according to the domain.
A decimal overflows: choose an appropriate precision and confirm financial rounding rules.

Do not add INSERT IGNORE globally as a shortcut. In strict mode, IGNORE can turn errors into warnings and adjusted values. Use it only when the statement's documented skip/adjust semantics are intentional and warnings are handled.

Handle zero and invalid dates deliberately

High-impact date modes include:

NO_ZERO_DATE: rejects 0000-00-00 in strict mode.
NO_ZERO_IN_DATE: rejects month or day components equal to zero in strict mode.
ALLOW_INVALID_DATES: allows some calendar-invalid day values and can create confusing behavior after the mode changes.

Audit relevant columns:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE DATA_TYPE IN ('date','datetime','timestamp')
  AND TABLE_SCHEMA NOT IN
    ('mysql','information_schema','performance_schema','sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Search application schemas for zero dates before enabling stricter rules. Use a table-specific query because comparing date types to zero-date literals can itself behave differently under candidate modes:

SELECT COUNT(*)
FROM legacy.orders
WHERE shipped_at = '0000-00-00 00:00:00';

Run legacy discovery under a controlled session whose mode can read the values, and never alter production data before business owners define the replacement. Possible outcomes are NULL, a reconstructed date from a trusted source, a separate status field, or quarantine for review.

Zero dates often encode “unknown,” “not applicable,” or “not processed.” Those meanings are not interchangeable. A blind update to 1970-01-01 simply replaces one sentinel with another.

Use ONLY_FULL_GROUP_BY to expose ambiguous queries

ONLY_FULL_GROUP_BY rejects grouped queries that select nonaggregated columns not included in the GROUP BY. Consider:

SELECT customer_id, status, MAX(created_at)
FROM orders
GROUP BY customer_id;

Which status belongs in the result? It is not necessarily the status from the row with the maximum timestamp. A permissive server can return a value chosen from the group, making the report nondeterministic.

Correct the query according to business intent. If the requirement is the latest order row per customer, one approach uses a window function on supporting releases:

WITH ranked AS (
  SELECT customer_id, status, created_at,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders
)
SELECT customer_id, status, created_at
FROM ranked
WHERE rn = 1;

The id tie-breaker makes ordering deterministic when timestamps match. Another design may aggregate status intentionally or join to a subquery. Do not silence the error by wrapping arbitrary columns in MIN() unless that matches the requirement.

Search reporting, ORM-generated SQL, and administrative dashboards. ONLY_FULL_GROUP_BY migrations often reveal bugs hidden for years.

Understand quoting modes

ANSI_QUOTES makes double quotes act as identifier quotes rather than string quotes. Under ordinary MariaDB behavior:

SELECT "hello";

may represent a string. With ANSI_QUOTES, it is treated as an identifier and may fail if no such column exists. Applications should use single quotes for string literals and backticks or driver-supported identifier quoting where necessary.

PIPES_AS_CONCAT changes || from logical OR behavior to string concatenation. IGNORE_SPACE affects how built-in function names followed by spaces are parsed. Combined modes such as ANSI enable several flags together and can alter SHOW CREATE TABLE output.

Do not enable a composite compatibility mode merely for one preferred syntax. Test every behavior it activates.

Understand arithmetic modes

ERROR_FOR_DIVISION_BY_ZERO controls warnings or errors for division by zero in data-changing contexts depending on strict behavior. Test both plain SELECT expressions and inserts or updates:

SET SESSION sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO';

SELECT 10 / 0;
SHOW WARNINGS;

Application code should still validate denominators and define business behavior. SQL mode is a backstop, not the only rule.

NO_UNSIGNED_SUBTRACTION makes subtraction results signed even with unsigned operands. Enabling it can change numeric expression results and overflow behavior. Include arithmetic boundary tests when unsigned columns appear in pricing, inventory, counters, or identifiers.

Do not confuse SQL_MODE with OLD_MODE

sql_mode changes current SQL parsing and behavior, including compatibility families such as ANSI, MSSQL, and ORACLE. old_mode enables selected older MariaDB or MySQL-compatible behaviors.

Inventory both:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode,
       @@GLOBAL.old_mode, @@SESSION.old_mode;

Do not use old_mode as a general upgrade escape hatch. Each flag can preserve behavior that is deprecated or removed later. Record a removal plan and regression test.

SQL_MODE=ORACLE is not a cosmetic quote setting. From MariaDB 10.3 it enables a substantial Oracle PL/SQL compatibility grammar and includes multiple component modes. Deploy it only for an intentionally migrated workload, not a shared server whose ordinary MariaDB routines were never tested under it.

Audit stored programs and views

MariaDB stores the sql_mode active when a view, trigger, event, procedure, or function is created. The object continues to use that creation-time mode even when the caller's session or global mode later changes.

Inventory routines:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
       SQL_MODE, DEFINER, SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA NOT IN
  ('mysql','information_schema','performance_schema','sys')
ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME;

Inspect exact definitions:

SHOW CREATE PROCEDURE orders.close_order;
SHOW CREATE FUNCTION orders.calculate_total;
SHOW CREATE VIEW orders.daily_summary;
SHOW CREATE TRIGGER orders.before_insert;
SHOW CREATE EVENT orders.daily_cleanup;

SHOW CREATE includes mode and connection metadata for relevant objects. Access requirements vary by release and privilege; MariaDB 11.3 introduced SHOW CREATE ROUTINE privilege.

A global change does not modernize old objects. If policy requires a routine to run under the new mode, recreate it under controlled session settings. Preserve definer, SQL SECURITY, deterministic declaration, data-access characteristic, comments, grants, character set, collation, and dependencies.

Do not bulk dump and reload routines without reviewing dump headers. Dump tools commonly emit session SET statements to preserve creation semantics.

Build a compatibility test matrix

Test each application, not only a SQL console. Cover:

Category	Representative failure
Strings	Overlong value, invalid encoding, trailing spaces
Numbers	Overflow, unsigned negative, decimal scale
Dates	Zero date, invalid day, timezone boundary
Grouping	Nonaggregated selected column
Quoting	Double-quoted literal, reserved identifier
Arithmetic	Division by zero, unsigned subtraction
Engines	Requesting an unavailable storage engine
Bulk writes	Multi-row partial failure and warnings
Stored objects	Creation, invocation, dump, restore
ORM	Generated grouping, schema introspection, migrations

Run positive business tests and intentional invalid cases. Assert error codes or SQLSTATE classes supported by the connector, transaction state, warnings, persisted values, and user-visible handling.

Check warnings immediately after the statement that produced them:

SHOW COUNT(*) WARNINGS;
SHOW WARNINGS LIMIT 100;

Another statement can replace the diagnostics. Application tests should capture connector warnings when the API supports them.

Use a restored production data copy to run read queries and migrations under the candidate session mode. Synthetic fixtures rarely contain historical zero dates, truncated strings, unusual defaults, or old stored objects.

Detect data that permissive behavior allowed

The exact audit depends on schema. Generate leads from metadata:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN
  ('mysql','information_schema','performance_schema','sys')
  AND (
    DATA_TYPE IN ('date','datetime','timestamp')
    OR DATA_TYPE IN ('tinyint','smallint','mediumint','int','bigint','decimal')
    OR DATA_TYPE IN ('char','varchar')
  )
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Then write table-specific queries for:

Zero and invalid sentinel dates.
Values at numeric boundaries.
Text exactly at maximum length, which may indicate prior truncation.
Invalid enum values or empty strings used instead of NULL.
Duplicates hidden by normalization assumptions.
Grouped reports with ambiguous selected columns.

MariaDB cannot tell whether a five-character stored value was intentionally five characters or truncated from eight. Compare source systems, application logs, rejected-message queues, and business invariants.

Repair data through reviewed migrations with row counts, samples, backups, and reconciliation. Do not enable a new strict mode and hope errors reveal every historical defect; strict validation applies when statements execute, not retroactively to every row.

Stage the change per workload

A safe rollout sequence is:

Capture current global, session, application, and stored-object modes.
Define a candidate from explicit correctness requirements.
Run code tests and a restored production data set under SET SESSION.
Fix invalid writes, ambiguous queries, dates, and quoting.
Deploy candidate session mode to a small application cohort.
Monitor errors, warnings, latency, and data invariants.
Expand by workload while preserving a control group.
Change the global default for new sessions.
Persist it in an owned option file.
Recycle connection pools and verify sessions.
Recreate stored objects only where approved.
Remove temporary per-application overrides.

Session-level canaries isolate risk, but they must exercise the same query paths. A read-only canary cannot validate strict writes.

When several applications share one MariaDB server, one may be ready before another. Temporary session overrides can support migration, but indefinite mode fragmentation increases operational complexity. Assign owners and deadlines.

Add and remove one mode safely

Avoid string replacement that can remove partial names. To add a known mode in a controlled session, first inspect the current list. MariaDB documentation shows set-style expressions such as:

SET SESSION sql_mode = CONCAT(@@SESSION.sql_mode, ',ONLY_FULL_GROUP_BY');

That can duplicate a flag or produce a leading comma when empty. A clearer deployment sets the complete reviewed canonical string:

SET SESSION sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
   NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';

SQL mode strings should be comma-separated without spaces for maximum clarity in configuration. The line wrapping above is explanatory; application code should send a valid single string.

For automated manipulation, split on commas, compare exact case-insensitive tokens, validate each token against the target release, sort or preserve a canonical order, and rejoin. Never remove STRICT with a regular expression that can alter both strict flags unpredictably.

Persist the server default

On Ubuntu, create a late-loading custom file:

[mariadb]
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY

Inspect precedence:

sudo rg -n '^[[:space:]]*sql[-_]mode[[:space:]]*=' /etc/mysql
my_print_defaults server mysqld mariadb mariadbd | 
  grep -E 'sql[-_]mode'

The file setting applies at startup. If the global value was already changed dynamically, decide whether to restart now or later. In either case, verify restart persistence during an approved window:

sudo systemctl restart mariadb
sudo systemctl --no-pager --full status mariadb
sudo journalctl -u mariadb -n 100 --no-pager

Then open a new connection:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

Existing connection pools must be recycled or validated. A dashboard showing only global mode can say rollout is complete while most application sessions still use the previous value.

Monitor the rollout

Track:

Error count by SQLSTATE, error code, service, and release.
Warnings returned by migration and batch tools.
Failed transactions and rollback rate.
HTTP or job failures tied to database validation.
Dead-letter queue growth.
Query latency and pool utilization.
Data-quality invariants such as zero dates or out-of-range values.
Stored routine, event, and report failures.
Replica and Galera health.

Sanitize SQL and parameters in logs. Strict errors can include column names and values that contain personal or secret data.

Establish a baseline before rollout. An error increase is meaningful only when the application previously counted and classified database failures.

Troubleshoot common errors

Data too long for column

Inspect the schema and actual value length in characters and bytes. Decide whether the domain permits the value. Expand through reviewed DDL or reject it in the application. Do not disable strict mode.

Out of range value

Check signedness, numeric precision, scale, unit conversion, and arithmetic overflow. A quantity of -1 in an unsigned column may signal a sentinel or a business error.

Column is not in GROUP BY

Rewrite the query to define which row or aggregate supplies every selected value. Add deterministic tie-breakers. Do not use an arbitrary aggregate solely to silence the error.

Invalid or zero date

Identify what the sentinel means, clean historical rows under a controlled migration, allow NULL if appropriate, and fix parsers. Do not replace all unknown dates with Unix epoch automatically.

Unknown SQL mode

The candidate contains a MySQL-only, removed, misspelled, or newer MariaDB flag. Query version and official documentation. Do not use an option prefix that silently ignores unknown modes.

Application still shows old behavior

Its pooled sessions inherited the old global value or execute their own SET SESSION. Query mode through the exact application connection and recycle the pool.

Routine behaves differently from ad hoc SQL

Inspect SHOW CREATE and Information Schema SQL_MODE. Stored objects use creation-time mode. Recreate only after testing and preserving metadata.

Dump restore fails under candidate mode

Inspect the dump's session mode statements, object definitions, quoting, and invalid data. Use version-matched tools and a clean restored test. Do not edit a large dump with uncontrolled search-and-replace.

Roll back without losing the lesson

Prepare the previous canonical mode before deployment. A runtime rollback for new sessions is:

SET GLOBAL sql_mode =
  'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
   NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Update the option file to match and recycle affected pools. If only one application fails, revert its session override while other validated workloads remain on the candidate.

Rollback does not undo data already inserted, adjusted, rejected, or updated under a different mode. Reconcile failed messages, partial work on nontransactional tables, and user operations. Preserve the compatibility test that failed, then fix the code and retry the migration.

Avoid reverting all the way to permissive behavior unless an approved incident decision requires it. Remove only the mode causing the verified incompatibility when possible.

Production best practices

Specify an intentional canonical mode instead of relying on defaults.
Keep strict mode for transactional data-quality enforcement.
Fix application and schema defects rather than disabling validation.
Test ONLY_FULL_GROUP_BY with every report and ORM.
Clean zero dates according to business meaning.
Treat composite modes as bundles with many effects.
Inventory session overrides and pooled connections.
Audit stored-object creation modes before global changes.
Test dumps, restores, migrations, replicas, and failover.
Roll out with session canaries and real writes.
Monitor errors, warnings, rollback, and data invariants.
Persist the approved value and verify after restart.
Keep exact-token automation and version gating.
Preserve a targeted rollback and remediation backlog.

FAQ

What is the default MariaDB SQL mode?

MariaDB 10.2.4 and later document a default including STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION. Query the actual server.

Is strict mode recommended for MariaDB?

Yes for most InnoDB applications because it rejects invalid writes instead of silently adjusting values. Test and fix compatibility issues before rollout.

Does `SET GLOBAL sql_mode` affect existing connections?

No. New connections inherit it. Existing sessions retain their session value until changed or disconnected.

Why does ONLY_FULL_GROUP_BY break reports?

It rejects ambiguous grouped queries that select a nonaggregated column without grouping it. Rewrite the query to express the intended row or aggregate.

Does changing global SQL mode alter stored procedures?

No. Stored programs and views retain the mode active at creation. Inspect and recreate them deliberately if policy requires a new mode.

Should I use INSERT IGNORE to bypass strict errors?

Only when adjusted or skipped rows are an intentional, monitored part of the operation. It can turn valuable errors into warnings and data changes.

Is SQL_MODE=ORACLE safe for ordinary MariaDB applications?

Not without full testing. It enables multiple compatibility flags and, from MariaDB 10.3, significant Oracle PL/SQL grammar.

Can SQL mode fix historical bad data?

No. It controls statement behavior going forward. Existing invalid or sentinel values require explicit discovery, business decisions, and migration.

Conclusion

A production MariaDB SQL mode should encode intentional correctness and compatibility rules. Inventory every server, session override, pool, migration tool, and stored object; then test strict writes, grouped queries, dates, quoting, arithmetic, dumps, and restores against realistic data.

Roll out with new-session canaries, fix the defects that stricter behavior exposes, and persist one reviewed canonical mode. A targeted rollback can protect availability, but the failed case belongs in the test suite so validation becomes stronger rather than quietly disappearing.

Understand global and session scope

Inventory every source of SQL mode

Know the default but do not depend on it

Understand strict mode

Prefer fixing data over disabling strict mode

Handle zero and invalid dates deliberately

Use ONLY_FULL_GROUP_BY to expose ambiguous queries

Understand quoting modes

Understand arithmetic modes

Do not confuse SQL_MODE with OLD_MODE

Audit stored programs and views

Build a compatibility test matrix

Detect data that permissive behavior allowed

Stage the change per workload

Add and remove one mode safely

Persist the server default

Monitor the rollout

Troubleshoot common errors

Data too long for column

Out of range value

Column is not in GROUP BY

Invalid or zero date

Unknown SQL mode

Application still shows old behavior

Routine behaves differently from ad hoc SQL

Dump restore fails under candidate mode

Roll back without losing the lesson

Production best practices

FAQ

What is the default MariaDB SQL mode?

Is strict mode recommended for MariaDB?

Does SET GLOBAL sql_mode affect existing connections?

Why does ONLY_FULL_GROUP_BY break reports?

Does changing global SQL mode alter stored procedures?

Should I use INSERT IGNORE to bypass strict errors?

Is SQL_MODE=ORACLE safe for ordinary MariaDB applications?

Can SQL mode fix historical bad data?

Conclusion

Suggested Internal Links

Suggested External Sources

Similar Posts

HELP

INFO

CONTACT INFO

Join our newsletter and get 20% off your first order

Wait! before you leave…

Recommended Products

Ask a question

Share

Shopping Cart

Your cart is empty

NAVIGATION

Does `SET GLOBAL sql_mode` affect existing connections?

Join our newsletter and get
20% off your first order