MySQL :: Improvements to STRICT MODE in MySQL

As a part of improving the error handling, in MySQL 5.7.5 we have re-implemented STRICT sql mode.

STRICT mode in MySQL affects the errors that arise from invalid, missing, or out of range values in DML statements such as INSERT, UPDATE, and DELETE. The new implementation aims to make the behavior of STRICT mode more consistent, yet maintain backward compatibility as much as possible.

In MySQL 5.7.5, we have made three general improvements to STRICT mode behavior:

1. STRICT mode got simpler

It was observed that having a large number of sql modes dependent on STRICT mode creates confusion among users. Specifically, we are talking about NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes. You can se further details on the above sql modes here. These modes only took effect if STRICT mode was also enabled, otherwise they produced warnings.

In MySQL 5.6 we deprecated these three modes and in 5.7.4 we have made their functionality part of STRICT mode itself. Basically the effects of enabling these three modes have been folded into STRICT mode. However, these modes are still retained due to upgrade issues and for backward compatibility. These modes will be completely removed in 5.8. Setting these modes will have no effect from 5.7.4 onwards. More details can be found here: WL#7467. Also you can check the documentation for this task here.

2. STRICT mode got better

It was also observed that the implementation of STRICT mode was not very good, and we got many related bug reports. For example, see Bug #42910 (triggers override strict sql_mode).

The behavior of Stored Routines with respect to STRICT mode was not consistent either. Sometimes, they were allowed to perform actions which are otherwise prohibited in STRICT mode.

The problem was that there was no central place in the code where the statements and errors affected by STRICT mode were handled. The code had the abort_on_warning flag which was switched off and on at various places across the codebase, making the implementation hard to understand and error prone.

In MySQL 5.7.4, we have re-implemented STRICT mode. The usage of the abort_on_warning flag is completely removed. Now, for each statement affected by STRICT mode, we push an error handler which is active during execution of the statement, and pop it when the statement execution is finished. There is also now a central place in the code where all errors that are generated during statement execution, and which are affected by STRICT mode, are handled. For additional information, you can look here: WL#6891.

Following is the list of errors affected by STRICT mode:

ER_TRUNCATED_WRONG_VALUE – “Truncated incorrect value”
ER_WRONG_VALUE_FOR_TYPE – “Incorrect value for function”
ER_WARN_DATA_OUT_OF_RANGE – “Out of range value”
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD – “Incorrect value for column at row”
WARN_DATA_TRUNCATED – “Data truncated for column”
ER_DATA_TOO_LONG – “Data too long for column”
ER_BAD_NULL_ERROR – “Column cannot be null”
ER_DIVISION_BY_ZERO – “Division by 0”
ER_NO_DEFAULT_FOR_FIELD – “Field doesn’t have a default value”
ER_NO_DEFAULT_FOR_VIEW_FIELD – “Field of view underlying table doesn’t have a default value”
ER_CUT_VALUE_GROUP_CONCAT – “Row was cut by GROUP_CONCAT()”
ER_DATETIME_FUNCTION_OVERFLOW – “Datetime function field overflow”
ER_WARN_NULL_TO_NOTNULL – “Column set to default value: NULL supplied to NOT NULL column”
ER_WARN_TOO_FEW_RECORDS – “Row doesn’t contain data for all columns”
ER_TOO_LONG_KEY – “Specified key was too long”
ER_WRONG_ARGUMENTS – “Incorrect arguments”
ER_INVALID_ARGUMENT_FOR_LOGARITHM – “Invalid argument for logarithm”

STRICT mode applies to the following types of DML statements:

INSERT
UPDATE
DELETE
LOAD DATA
ALTER TABLE
INSERT… SELECT
CREATE TABLE
CREATE INDEX
CREATE TABLE… SELECT
SELECT sleep()

3. STRICT mode is the default

We have decided to add STRICT_TRANS_TABLES to the list of default sql modes in MySQL 5.7.5. (WL#7764).

Previously, if the user tried to insert 15 characters into a CHAR(10) column, then by default (STRICT Mode OFF) it would insert the first 10 characters, present the user with a warning, and then throw away the remaining five characters. Now the default behaviour (STRICT Mode ON) will be that the above statement will be rejected with an error.

Lots of changes have been made to existing test cases so that they are run with the new default SQL MODE. Around 500 mtr testcases were updated as part of this work.

WL#7467, WL#6891, WL#7764 were designed and implemented by Raghav Kapoor.