sql_mode

Overrides the default behavior of the server in several contexts.

See also: System Variables for MariaDB Enterprise Server 10.6, in 10.5 ES, and in 10.4 ES

USAGE

The sql_mode system variable can have its default value configured via SkySQL Configuration Manager.

DETAILS

The sql_mode system variable can be used to override the default behavior of the server in several contexts, such as:

  • How SQL statements are parsed.

  • How SQL statements are executed.

  • How SQL statements handle errors.

  • How SQL statements interpret data types.

The options for the sql_mode system variable are divided into two categories:

  • Base sql_mode options.

  • Alias sql_mode options.

The list of base options for the sql_mode system variable are listed in the following table:

SQL Mode

Description

REAL_AS_FLOAT

This mode makes the REAL data type become an alias for the FLOAT data type, rather than an alias for the DOUBLE data type.

PIPES_AS_CONCAT

This mode makes the || operator become functionally equivalent to the CONCAT() function, rather than functionally equivalent to the OR operator. This means that a || b would be functionally equivalent to CONCAT(a, b).

ANSI_QUOTES

This mode makes the " character become the identifier quote character, instead of the ` character. This is more compatible with the ANSI standard, but it may break applications developed for MariaDB if they use the " character to quote string literals.

IGNORE_SPACE

This mode allows spaces to appear between function names and parenthesis. However, a side effect is that the names of built-in functions become reserved words.

IGNORE_BAD_TABLE_OPTIONS

This mode causes bad table options provided to the CREATE TABLE and ALTER TABLE statements to generate warnings instead of errors.

ONLY_FULL_GROUP_BY

This mode disallows columns to be selected in SELECT ... GROUP BY statements, unless the column is in the GROUP BY clause or the column is provided to an aggregate function, such as the COUNT() function.

NO_UNSIGNED_SUBTRACTION

This mode forces subtraction results to use signed data types, even if the operands use unsigned data types.

NO_DIR_IN_CREATE

This mode causes the DATA DIRECTORY and INDEX DIRECTORY table options to be ignored in the CREATE TABLE and ALTER TABLE statements.

NO_KEY_OPTIONS

This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific index options.

NO_TABLE_OPTIONS

This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific table options.

NO_FIELD_OPTIONS

This mode prevents the SHOW CREATE TABLE statement from printing MariaDB-specific column options.

NO_AUTO_VALUE_ON_ZERO

This mode does not interpret the value 0 the same way as NULL for AUTO_INCREMENT columns. Instead, it is treated as an actual 0.

NO_BACKSLASH_ESCAPES

This mode does not allow the backslash character () to be used to escape characters within strings. Instead, it is just a regular character.

STRICT_TRANS_TABLES

This mode enables strict mode for tables that use transactional storage engines. In strict mode, any statements that involve invalid or missing data will generate errors instead of warnings, and the statement will be aborted and rolled back.

STRICT_ALL_TABLES

This mode enables strict mode for tables that use transactional and non-transactional storage engines. In strict mode, any statements that involve invalid or missing data will generate errors instead of warnings. With non-transactional storage engines, statements cannot be rolled back, so if the error occurs after the first row, then the statement may only be partially completed.

NO_ZERO_IN_DATE

This mode prohibits the DATE data type from having a non-zero value for the year component when the date does have zero values for the month or day components. For example, '0000-00-000' would be allowed, but '2020-01-00', '2020-00-01', and '2020-00-00' would be prohibited.

NO_ZERO_DATE

This mode prohibits '0000-00-00' as a valid value for the DATE data type.

ALLOW_INVALID_DATES

This mode allows the DATE data type to have any value in the range 1-31 in the day component, even if it isn't a valid date.

ERROR_FOR_DIVISION_BY_ZERO

This mode causes an error to be thrown when a number is divided by zero. Otherwise, NULL is returned.

NO_AUTO_CREATE_USER

This mode prohibits the GRANT statement from automatically creating a user, unless the statement also included authentication-related information, such as a password or an authentication plugin.

HIGH_NOT_PRECEDENCE

This mode increases the precedence of the NOT operator, so that NOT a BETWEEN b and c is interpreted as (NOT a) BETWEEN b and C. This mode is more compatible with MySQL 5.0.1 and before.

NO_ENGINE_SUBSTITUTION

This mode does not allow a storage engine to be substituted with the default. Usually, if a storage engine is specified in a CREATE TABLE or ALTER TABLE statement and that storage engine doesn't exist, then a warning with the ER_UNKNOWN_STORAGE_ENGINE error code will be raised, and the storage engine will be substituted with the default. This mode causes an error to be thrown instead.

PAD_CHAR_TO_FULL_LENGTH

This mode causes columns using the CHAR data type to be padded to the column's full length when the column is selected.

EMPTY_STRING_IS_NULL

This mode causes the empty string to be treated the same as NULL for Oracle compatibility.

SIMULTANEOUS_ASSIGNMENT

This mode causes the server to evaluate all assignments in the SET clause of an UPDATE statement at the same time, rather than from left-to-right.

TIME_ROUND_FRACTIONAL

This mode causes the server to round fractional seconds when the value is converted to a data type without the fractional seconds.

The list of alias options for the sql_mode system variable are listed in the following table:

SQL Mode

Description

ANSI

This mode is an alias for REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE.

DB2

This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MAXDB

This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

MSSQL

This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

MYSQL323

This mode is an alias for HIGH_NOT_PRECEDENCE.

MYSQL40

This mode is an alias for HIGH_NOT_PRECEDENCE.

ORACLE

This mode enables a subset of Oracle's PL/SQL language for stored procedures. It also enables the following other modes: PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER, SIMULTANEOUS_ASSIGNMENT.

POSTGRESQL

This mode is an alias for PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

TRADITIONAL

This mode is an alias for STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION.

PARAMETERS

Dynamic

Yes

Scope

Global, Session

Data Type

SET (REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, IGNORE_BAD_TABLE_OPTIONS, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION, NO_DIR_IN_CREATE, POSTGRESQL, ORACLE, MSSQL, DB2, MAXDB, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, MYSQL323, MYSQL40, ANSI, NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ALLOW_INVALID_DATES, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE, NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH, EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT, TIME_ROUND_FRACTIONAL, EXTENDED_ALIASES)

Default Value

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

SKYSQL

Cloud

SkySQL Topology

ES Ver

Default

AWS

HA (Primary/Replica)

10.4

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

AWS

HA (Primary/Replica)

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

AWS

Transactional Standalone

10.4

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

AWS

Transactional Standalone

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

AWS

Distributed Transactions

10.5

STRICT_TRANS_TABLES (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

ColumnStore

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

ColumnStore Multi

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

Galera

10.4

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

Galera

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

HA (Primary/Replica)

10.4

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

HA (Primary/Replica)

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

HTAP

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

Transactional Standalone

10.4

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

Transactional Standalone

10.5

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

GCP

Distributed Transactions

10.5

STRICT_TRANS_TABLES (choices: 0 or 1 of the following: DB2, MSSQL, ORACLE, POSTGRESQL)

PRIVILEGES

The sql_mode system variable requires the SUPER privilege to dynamically set its global value at runtime.

The sql_mode system variable does not require any special privilege to dynamically set its session value at runtime.

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES