Mysql 5.7 upgrade too strict

23 septembre 2016 par: Benoit Bonneville

Mysql 5.7 come with a new default settings: the strict mode.

This feature replace MySql previous warning by a Fatal error and it will crash your application in many cases :

  • Forgetting to defined default values for your field
  • Trying to set a empty string into a integer field
  • Dividing by 0

Errors Sample

  • Incorrect integer value: '' for column 'id' at row 1

How to fix

In order to disable Mysql Strict Mode you have to add this line into your configuration file my.cnf :

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Be sure to add this line under the [mysqld] section.

Unix permanent fix

In modern unix install version, the smartest solution is to create a file :
/etc/mysql/mysql.conf.d/mysqld_disable_strict_mode.cnf

File content :

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then
sudo service mysql restart

Why is it safer ?
All files into /etc/mysql/mysql.conf.d/ ending by .cnf extensions are loaded at MySQL startup.

If the Unix distribution update the default mysqld.cnf, it won’t modify your file.
So your settings will remains after.

Répondre