Menu

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.

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.

Thank you Rick for comment. We agree that some of the error messages listed here are less than perfect. But we are restrictive about changing the existing error messages since external tools can be relying on them. While adding new error messages, we see to it that they are clear and understandable.

Thank you Daniël for comment. We agree that some applications may need adjustment according to the changes related to STRICT mode. The changes are made to make MySQL more standard and safe to use. Simplification of the server sometimes has consequences like this. While disabling STRICT mode is one option, IGNORE keyword can be used in STRICT mode with supported statements. For more details see http://mysqlserverteam.com/improvements-to-the-mysql-ignore-implementation/