How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04

How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04
by Janeth Kent Date: 18-02-2019 ubuntu mysql linux

If your app was written for older versions of MySQL and is not compatible with strict SQL mode in MySQL 5.7, you can disable strict SQL mode. For example, apps such as WHMCS 6 and Craft 2 do not support strict SQL mode.

If you're using WHMCS 7, see our article on customizing MySQL for WHMCS 7.

To disable strict SQL mode, SSH in to your server as root and create this file:

/etc/mysql/conf.d/disable_strict_mode.cnf

Open the file and enter these two lines:

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

Restart MySQL with this command:

sudo service mysql restart

This change disables two SQL mode settings, STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY, that were added in MySQL 5.7 and cause problems for some older applications.

Confirming Strict SQL Mode Is Disabled

You can confirm strict SQL mode is disabled by running this command as root:

sudo mysql -i -BN -e 'SELECT @@sql_mode' | grep -E 'ONLY_FULL_GROUP_BY|STRICT_TRANS_TABLES'

If strict mode is disabled, you won't see any output from that command.

If disabling strict mode causes any problems for you, you can re-enable it by deleting that file and restarting MySQL again.

What Strict SQL Mode Errors Look Like

If your app isn't compatible with strict SQL mode, you'll often see SQL errors such as:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of
SELECT list is not in GROUP BY clause and contains nonaggregated column
'yourdbname.tblannouncements.date' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

 
by Janeth Kent Date: 18-02-2019 ubuntu mysql linux hits : 28488  
 
Janeth Kent

Janeth Kent

Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.

 
 
 

Related Posts

How to fix excessive MySQL CPU usage

What happens if we realise that a series of databases that we thought were optimised through the use of indexes, have begun to consume CPU usage time of a server…

How to connect to MySQL with Node.js

Let's see how you can connect to a MySQL database using Node.js, the popular JavaScript runtime environment. Before we start, it is important to note that you must have Node.js installed…

How To Use Varnish As A Highly Available Load Balancer On Ubuntu 20.04 With SSL

Load balancing with high availability can be tough to set up. Fortunately, Varnish HTTP Cache server provides a dead simple highly available load balancer that will also work as a…

htaccess Rules to Help Protect from SQL Injections and XSS

This list of rules by no means is a sure bet to secure your web services, but it will help in preventing script-kiddings from doing some basic browsing around. MySQL injection…

MySQL 8.0 is now fully supported in PHP 7.4

MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules…

Linux For Dummies: Permissions

In the previous articles I made a short introduction to the Unix world and in the following article I have dealt with the basic commands for the file system management. Today we are…

Linux for Dummies: Ubuntu Terminal

I introduced in the previous article, available here, the basic concepts concerning the Linux world. Today we are going to have a look to some basic operations that we can perform…

The Best RSS Readers for Ubuntu

Even if most of the tech experts actively claim that RSS (Rich Site Summary) is dead especially after Google Reader was discontinued 5 years ago but it isn’t yet as…

How to install Letsencrypt Certificates with Certbot in Ubuntu

In this article we will explain how to install, manage and configure the SSL Security certificate, Let's Encypt in NGINX server used as proxy. This certificate is free but does…

How to Set up a Fully Functional Mail Server on Ubuntu 16.04 with iRedMail

Setting up your own mail server from scratch on Linux is complex and tedious, until you meet iRedMail. This tutorial is going to show you how you can easily and…

GIMP 2.10 released: Features 32-bit support, new UI and A Ton Of Improvements

It's been over a half-decade since the GIMP 2.8 stable debut and today marks the long-awaited release of GIMP 2.10, its first major update in six years. And among other…

Setting Up SFTP on Ubuntu 16.04

I recently had a request to setup SFTP for a customer so they could manage a set of files in their environment through an FTP GUI. Being an avid user…

Clicky