Disable Strict Mode for MySQL 5.7 on Ubuntu

I was working on an Ubuntu 16.04 LTS server with Virtualmin, Nginx, PHP 7 and MySQL 5.7 when I uncounted an error related to MySQL. Few MySQL queries failed and application I was working on displayed Uncaught Error: Call to a member function fetch_assoc() on boolean. Disabling Strict mode fixed the issue.

I was working on a Prosper202 installation which I installed for a client. I built the server with Virtualmin and Nginx. Although Ubuntu 16.04 comes with MariaDB v10, Virtualmin installs MySQL 5.7 with its installation script. Prosper202 displayed following error on campaign overview page.

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in /home/yesmediapro/public_html/202-config/class-dataengine.php:421 Stack trace: #0 /home/yesmediapro/public_html/202-config/class-dataengine.php(62): DataEngine->doCampaignOverviewReport('1474329600', '1474415999', false) #1 /home/yesmediapro/public_html/tracking202/ajax/account_overview.php(126): DataEngine->getReportData('campaignOvervie...', '1474329600', '1474415999', false) #2 {main} thrown in /home/yesmediapro/public_html/202-config/class-dataengine.php on line 421

It didn’t take long for me to realize that this is due to MySQL Strict mode. Prosper202 is not compatible with MySQL strict mode. Disabling it fixed the error. Other applications that are not compatible with MySQL Strict Mode include WHMCS 6 and Craft 2. So if your app is misbehaving, you should consider disabling Strict mode.

What is MySQL Strict Mode?

Strict mode handles invalid or missing values in SQL queries. It’s available in both MySQL and MariaDB and enabled by default in MySQL 5.7 and MariaDB 10. Invalid or missing data in queries will cause warnings or errors in strict mode while, invalid or missing values will be adjusted and would produce a simple warning on strict mode turned off. Although disabling it is safe on most cases, sometimes it may cause unexpected errors in database tables.

How to Disable MySQL Strict Mode?

It’s relatively easy to disable MySQL strict mode in Ubuntu 16.04 LTS. This may work on other versions of Linux such as CentOS. But I’m not testing it. You can let others know if it’s working on the comments section. Start by login in to your VPS as root user with SSH.

Create a new .cnf file.

nano /etc/mysql/conf.d/strict_mode.cnf

Paste the following code into that file,

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

Save and close the file. And then restart MySQL server.

systemctl restart mysql

Open your application and errors should have disappeared.

How to Enable MySQL Strict Mode back on?

You can turn strict mode back on in the future if your application become compatible with it. Just deleting the file, you created earlier and the restarting MySQL will turn strict mode back on. Following command will delete the file,

rm /etc/mysql/conf.d/strict_mode.cnf

Restart MySQL,

systemctl restart mysql

That’s it. Feel free to use comments section if you’re having trouble disabling or re-enabling MySQL strict mode.