STRICT_ALL_TABLES

Overview

This mode enables strict mode for all tables.

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. While the STRICT_TRANS_TABLES mode can also be set, it imparts no additional behavior changes beyond this mode.

USAGE

DETAILS

The STRICT_ALL_TABLES mode enables strict mode for all tables. This mode is equivalent to STRICT_TRANS_TABLES, because all tables are transactional in Xpand.

Data Type Exceptions

In MariaDB Xpand, strict mode does not enable validation and range checks for all data types:

To upgrade to a newer version of Xpand, see "Upgrades for MariaDB Xpand".

To request help with the upgrade, please contact MariaDB Support.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

Setting the SQL_MODE

Note that @@sql_mode is the same as @@session.sql_mode:

SELECT @@sql_mode, @@session.sql_mode;
+---------------------+---------------------+
| @@sql_mode          | @@session.sql_mode  |
+---------------------+---------------------+
| STRICT_TRANS_TABLES | STRICT_TRANS_TABLES |
+---------------------+---------------------+

The following adds the STRICT_ALL_TABLES mode to the existing SQL_MODE session value:

SET sql_mode = CONCAT(@@sql_mode, ',STRICT_ALL_TABLES');
SELECT @@sql_mode;
+---------------------------------------+
| @@sql_mode                            |
+---------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------------------------------+

The mode can also be set on its own:

SET sql_mode = 'STRICT_ALL_TABLES';

Rejecting Invalid Values

This table will let us test validating some integer values:

CREATE TABLE strict_test (
  j TINYINT,
  descr varchar(128)
);

These inserted values are rejected when STRICT_ALL_TABLES is set due to one row having an integer overflow:

INSERT INTO strict_test VALUES
   (0, 'inserted 0'),
   (1, 'inserted 1'),
   (2000, 'inserted 2000');
ERROR 1264 (HY000): [63494] Value out of range: "2000 overflowed TINYINT"

Inserting Invalid Values

To allow invalid values to be inserted as truncated values instead of being rejected, the SQL_MODE flags must not contain a flag that enables strict tables. One way to do that is to assign an empty string to the SQL_MODE flags:

SET sql_mode = '';
INSERT INTO strict_test VALUES
   (0, 'inserted 0'),
   (1, 'inserted 1'),
   (2000, 'inserted 2000');
SELECT * FROM strict_test;
+------+---------------+
| j    | descr         |
+------+---------------+
|    0 | inserted 0    |
|    1 | inserted 1    |
|  127 | inserted 2000 |
+------+---------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES