Starting from MariaDB 10.2 and MySQL 5.7, the default SQL Mode will be changed –

From the original default “NO_ENGINE_SUBSTITUTION”,

Change to “ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.”

The change of STRICT_TRANS_TABLES is more obvious

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.

For example –
If a NOT-NULL column (or called field) with no default value, when adding a record, if no data is provided for this field,
In the past, it was automatically written as NULL by default, but you can add records;
Now, according to STRICT_TRANS_TABLES, it cannot add new record, it raises an error (exception)

For details, please refer to-
https://mariadb.com/kb/en/sql-mode/
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

If necessary, you may manually add the following SQL in your CMS application to fallback to legacy SQL Mode

set SQL_MODE = ‘NO_ENGINE_SUBSTITUTION’;

Pin It on Pinterest

Share This