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 and OLD_MODE.

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;

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

The different values of SQL_MODE are:

OptionDescription
ALLOW_INVALID_DATESAllow any day between 1-31 in the day part. This is convenient when you want to read in all (including wrong data) into the database and then manipulate it there.
ANSIChanges the SQL syntax to be closer to ANSI SQL. Sets:

REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE

It also adds a restriction: an error will be returned if a subquery uses an aggregating function with a reference to a column from an outer query in a way that cannot be resolved.
ANSI_QUOTESChanges " to be treated as `, the identifier quote character. This may break old MariaDB applications which assume that " is used as a string quote character.
DB2Same as:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, DB2, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS

ERROR_FOR_DIVISION_BY_ZEROReturn a 1365 'Division by 0' error (instead of a warning) if division by zero error occurs. If this is not set division by zero returns NULL.
HIGH_NOT_PRECEDENCECompatibility option for MySQL 5.0.1 and before; This changes NOT a BETWEEN b AND c to be parsed as (NOT a) BETWEEN a AND c
IGNORE_BAD_TABLE_OPTIONSIf this is set generate a warning (not an error) for wrong table option to CREATE TABLE.
IGNORE_SPACEAllow one to have spaces (including tab characters and new line characters) between function name and '('. The drawback is that this causes built in functions to become reserved words.
MAXDBSame as:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MAXDB, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

MSSQLSame as:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, MSSQL, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS

MYSQL323Same as:

NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE

MYSQL40Same as:

NO_FIELD_OPTIONS, HIGH_NOT_PRECEDENCE

NO_AUTO_CREATE_USERDon't automatically create users with GRANT. Produce a 1133 error: "Can't find any matching row in the user table".
NO_AUTO_VALUE_ON_ZEROIf set don't generate an AUTO_INCREMENT on INSERT of zero in an AUTO_INCREMENT column. Normally both zero and NULL generate new AUTO_INCREMENT values.
NO_BACKSLASH_ESCAPESDisables using the backslash character \ as an escape character within strings, making it equivalent to an ordinary character.
NO_DIR_IN_CREATEIgnore all INDEX DIRECTORY and DATA DIRECTORY directives when creating a table. Can be useful on slave replication servers.
NO_ENGINE_SUBSTITUTIONIf set, generate a 1286 error when creating a table if the specified storage engine is not available.
NO_FIELD_OPTIONSRemove MariaDB-specific column options from the output of SHOW CREATE TABLE. This is also used by the portability mode of mysqldump.
NO_KEY_OPTIONSRemove MariaDB-specific index options from the output of SHOW CREATE TABLE. This is also used by the portability mode of mysqldump.
NO_TABLE_OPTIONSRemove MariaDB-specific table options from the output of SHOW CREATE TABLE. This is also used by the portability mode of mysqldump.
NO_UNSIGNED_SUBTRACTIONWhen enabled, subtraction results are signed even if the operands are unsigned.
NO_ZERO_DATEDon't allow '000-00-00' as a valid date in strict mode (produce a 1525 error). Zero dates can be inserted with IGNORE. If not in strict mode, a warning is generated.
NO_ZERO_IN_DATEDon't allow dates where the year is not zero but the month or day parts of the date are zero (produce a 1525 error). For example, with this set, '0000-00-00' is allowed, but '1970-00-10' or '1929-01-00' are not. If the ignore option is used, MariaDB will insert '0000-00-00' for those types of dates. If not in strict mode, a warning is generated instead.
ONLY_FULL_GROUP_BYFor SELECT ... GROUP BY queries, disallow SELECTing columns which are not referred to in the GROUP BY clause, unless they are passed to an aggregate function like COUNT() or MAX(). Produce a 1055 error.
ORACLESame as:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ORACLE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS,N O_AUTO_CREATE_USER

PAD_CHAR_TO_FULL_LENGTHTrailing spaces in CHAR columns are by default trimmed upon retrieval. With PAD_CHAR_TO_FULL_LENGTH enabled, no trimming occurs. Does not apply to VARCHARs.
PIPES_AS_CONCATAllows using the pipe character (ASCII 124) as string concatenation operator.
POSTGRESQLSame as:

PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, POSTGRESQL, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS

REAL_AS_FLOATREAL is a synonym for FLOAT rather than DOUBLE.
STRICT_ALL_TABLESStrict 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.
STRICT_TRANS_TABLESStrict mode. Statements with invalid or missing data are aborted and rolled back, except that for non-transactional storage engines and statements affecting multiple rows where the invalid or missing data is not the first row, MariaDB will convert the invalid value to the closest valid value, or, if a value is missing, insert the column default value.
TRADITIONALMakes MariaDB work like a traditional SQL server. Same as:

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER

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

The default SQL_MODE is an empty string.

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 the 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 |
+----------------------------------+

Comments

Comments loading...