SQL_MODE

MariaDB supports several different modes which allow you to tune it to suit your needs.

The most important ways for doing this are using SQL_MODE (controlled by the sql_mode system variable) and OLD_MODE (the old_mode system variable). SQL_MODE is used for getting MariaDB to emulate behavior from other SQL servers, while OLD_MODE is used for emulating behavior from older MariaDB or MySQL versions.

SQL_MODEis a string with different options separated by commas (',') without spaces. The options are case insensitive.

You can check the local and global value of it with:

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

Setting SQL_MODE

Defaults

You can set the SQL_MODE either from the command line (the --sql-mode option) or by setting the sql_mode system variable.

SET sql_mode = 'modes';
SET GLOBAL sql_mode = 'modes';

The session value only affects the current client, and can be changed by the client when required. To set the global value, the SUPER privilege is required, and the change affects any clients that connect from that point on.

SQL_MODE Values

The different SQL_MODE values are:

Strict Mode

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode.

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.

With strict mode not set (default in version <= MariaDB 10.2.3), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.

Statements that don't modify data will return a warning when adjusted regardless of mode.

SQL_MODE and Stored Programs

Stored programs and views always use the SQL_MODE that was active when they were created. This means that users can safely change session or global SQL_MODE; the stored programs they use will still work as usual.

It is possible to change session SQL_MODE within a stored program. In this case, the new SQL_MODE will be in effect only in the body of the current stored program. If it calls some stored procedures, they will not be affected by the change.

Some Information Schema tables (such as ROUTINES) and SHOW CREATE statements such as SHOW CREATE PROCEDURE show the SQL_MODE used by the stored programs.

Examples

This example shows how to get a readable list of enabled SQL_MODE flags:

SELECT REPLACE(@@SQL_MODE, ',', '\n');
+-------------------------------------------------------------------------+
| REPLACE(@@SQL_MODE, ',', '\n')                                          |
+-------------------------------------------------------------------------+
| STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------+

Adding a new flag:

SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_ENGINE_SUBSTITUTION');

If the specified flag is already ON, the above example has no effect but does not produce an error.

How to unset a flag:

SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ENGINE_SUBSTITUTION', '');

How to check if a flag is set:

SELECT @@SQL_MODE LIKE '%NO_ZERO_DATE%';
+----------------------------------+
| @@SQL_MODE LIKE '%NO_ZERO_DATE%' |
+----------------------------------+
|                                1 |
+----------------------------------+

Without and with strict mode:

CREATE TABLE strict (s CHAR(5), n TINYINT);

INSERT INTO strict VALUES ('MariaDB', '128');
Query OK, 1 row affected, 2 warnings (0.14 sec)

SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 's' at row 1     |
| Warning | 1264 | Out of range value for column 'n' at row 1 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

SELECT * FROM strict;
+-------+------+
| s     | n    |
+-------+------+
| Maria |  127 |
+-------+------+

SET sql_mode='STRICT_TRANS_TABLES';

INSERT INTO strict VALUES ('MariaDB', '128');
ERROR 1406 (22001): Data too long for column 's' at row 1

Overriding strict mode with the IGNORE keyword:

INSERT IGNORE INTO strict VALUES ('MariaDB', '128');
Query OK, 1 row affected, 2 warnings (0.15 sec)