If your or your client’s application/site was written for older versions of MySQL and is not compatible with strict SQL mode in MySQL 5.7 and upwards, you can disable this mode with just a small modification to your config file.
I came across this issue while working for a client that had their site done in some random home copy/paste CMS project (the guy doing it was supposedly a pro mind you), and while fixing quite a few code issues I noticed an issue with the MySQL DB connectivity.
How do you know you have this issue? Well, most commonly the first error you encounter is something like this:
“Incorrect integer value ” for column ‘name’ at row 1″
OK let’s begin!
To disable strict SQL mode, SSH to your VPS or dedicated server. Firstly, we need to find out which config file your MySQL server is using by simply typing:
which mysqld
You will receive a reply something similar to:
/usr/sbin/mysqld
Now let’s check the exact location of the config file:
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Where you will receive a notification like:
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
Since I use cPanel on most of my servers the main config file is usually located at /etc/my.cnf but if for any reason this file is not present then check for /etc/mysql/my.cnf or ~/.my.cnf. One of them will be there and edit it with your favourite editor.
Before editing anything be sure to check what are the current settings of your server with a simple one-line command:
mysql -u root -p -e "select @@sql_mode"
Where something like this will show up:
+----------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------+ |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------+
Which tell us strict mode is currently on.
Now open the configuration file /etc/my.cnf and add the following line right after the [mysqld]
sql_mode = ""
Just save the file and reboot your MySQL. The strict mode is now disabled. Don’t worry If you need any of the other modes or functions enabled just put them in between the “”, but in my experience working with older scripts it’s just more simple to disable all of them.