MYSQL SERVER COMPATIBILITY – HOW TO DISABLE STRICT MODE
MySQL server offers several different SQL modes of operation, and can apply these modes differently for different clients, depending on the value of sql_mode system variable. Database administrators can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.
What Are MySQL SQL Modes?
Of course, you can read entire definition at MySQL Reference Manual page.
In plain english: sql_mode determines how MySQL server behaves for certain data types, how to handle specific things such as space or quote characters, errors or incomplete SQL statements, invalid date and data values upon INSERT and UPDATE commands and alike. It is very important to understand impact of these settings, as they can pretty easily break your entire application!
Since MySQL 5.7 server release, there are some important changes introduced that break compatibility with older version like 5.6 and 5.5. Applications might stop working properly or completely, if you (or your hosting provider) blindly upgrade to MySQL Server 5.7 without proper code revision.
To read about complete compatibility “mess” with different sql_mode directives in particular MySQL version (prior 5.7.4, between 5.7.4 ~ 5.7.7 and above 5.7.8) please read above reference manual link.
How do I disable strict mode on MySQL server? How to restore SQL compatibility in MySQL 5.7+ server?
STEP 1: Open MySQL Server my.cnf (or my.ini on Windows) configuration file. Depending on your OS / Dev Environment, it will be at a different location. Example for Ubuntu OS:
/etc/mysql/my.cnf
STEP 2: Look for the following / similar line (depends on particular MySQL version):
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'
STEP 3: Change To:
sql_mode = ''
STEP 4: If you don’t see or have that line / option, add it manually under [mysqld] section.
STEP 5: Restart the MySQL service (with following command under Ubuntu, for example):
mysqld service restart