Strict Mode enabled by default, How to disable Strict Mode after upgrade to MySQL or Maria DB (10.1 - 10.2)

Eduardo Ponce de Leon | 2018-11-13

Strict Mode enabled by default, How to disable Strict Mode after upgrade to MySQL or Maria DB (10.1 - 10.2)
MySQL / Maria DB Upgrade - Strict Mode is enabled by default, how to go back if your web applications start to crash

After Upgrading Maria DB from 10.1 to 10.2, Strict Mode becomes the default mode and this might create errors in your web applications if the table structures and queries have certain errors that before were only taken as Warnings.

 

From the MySQL documentation:

“Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.”

 

After the update is done, run this query to see if Strict Mode was enabled

 

SHOW VARIABLES LIKE 'sql_mode';

 

The result will be a comma separate string, if one of the comma separated values says "STRICT_TRANS_TABLE", then you are in strict mode.
 
To disable completely strict mode and all other settings, simply run this query.
NOTE: you need admin permission.
 
set global sql_mode='';

 

Although this will fix it, it will remove other important parameters that most probably were there even before the update. To set up the default value run this query:
 
set global sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

If you want to upgrade MySQL or Maria DB but are not sure if strict mode will affect your applications run this query and test:

 

set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

If your application broke go back to the previous configuration ('NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION')

 

If you already upgraded and are sure that Strict Mode is safe for you but you messed around with the sql_mode configuration, the default value after the upgrade, with strict mode and other strict features is:

 

set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

I hope this helps, share if you find it useful or leave us a comment below.

 

 

 

 

 

Commentaires