STRICT_TRANS_TABLES
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Modes for MariaDB Xpand
Topics on this page:
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:
Before Xpand 5.3.19, data types are not validated, and range checks are not performed.
Starting with Xpand 5.3.19, data types are validated, and range checks are performed for all types, except:
Starting with Xpand 6.1, data types are validated, and range checks are performed for all types, except:
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 |
+------+---------------+