STRICT_TRANS_TABLES

Overview

This mode enables strict mode for all tables because all Xpand tables 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. While the STRICT_ALL_TABLES mode can also be set, it imparts no additional behavior changes beyond this mode.

DETAILS

The STRICT_TRANS_TABLES mode enables strict mode for all tables. This mode is equivalent to STRICT_ALL_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.

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 STRICT_TRANS_TABLES flag is on by default, as shown above. In the following example, a request to add a flag that is already set shows how multiple identical flag values are reduced to a single SQL_MODE flag:

SET sql_mode = CONCAT(@@sql_mode, ',STRICT_TRANS_TABLES');
SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_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_TRANS_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 |
+------+---------------+

CHANGE HISTORY

Release Series

History

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.