Enforce Data Integrity with sql_mode

We have seen the default behaviour of MySQL where the server tries to help us by adjusting data that does not fit into a column until it is is acceptable. For example if trying to add the value of 255 to a signed TINYINT then MySQL will try to adjust the value to 127, the maximum value allowed for a signed TINYINT. Where this may e useful perhaps in some situations, in most, I would suggest that it is not useful and an error should be reported as such and if the data does not fit don’t let it in.

sql_mode

My default MySQL does not have any values stored in the variable sql_mode . On of the possible values that we can add to this is STRICT_ALL_TABLES. If multiple values need to be set we can comma separate them.

Setting the value at run time

We can configure this at run time using the set command:

SET SESSION sql_mode = ‘strict_all_tables’;

SET GLOBAL sql_mode = ‘strict_all_tables’;

Users can set their own session variable values and they effect just their own current session. If you are an administrator the GLOBAL value can be set and sets for the admin’s session and all new sessions established after this has been made until it is reset or the server is restarted. The current value can be read using SELECT

SELECT @@session.sql_mode;

The following screen shot shows the output and the strict mode being set.

Persisting this setting

If we wish the setting to persist we can add it to the /etc/my.cnf file in Linux or on Windows, C:Program FilesMySQLMySQL Server x.xmy.ini . Replace the x values in the Windows path with your version such as 5.5 , 5.6 etc. The settings file control both the client and the server so make sure we add the setting to the mysqld settings section. We should add the value:

sql-mode = “STRICT_ALL_TABLES”

We can see the setting in place in the following screen shot from my server:

On a server restart this setting will now become effective as the default without user intervention and data has to match the data-type defined in the column. Now trying to insert 255 into a signed TINYINT will fail: