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 : 21004  
 
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 END DEVELOPMENT.

 
 
 

Related Posts

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…

MYSQL: How to create a new user and give it full access only to 1 database

To create a new user in MySQL and give it full access only to 1 database, say dbTest, these are the MySQL commands to do that To create the user: CREATE USER…

Install Java in Ubuntu 16.04

Java and the JVM (Java's virtual machine) are widely used and required for many kinds of software. This article will guide you through the process of installing and managing different…

ArangoDB, install and configure the popular Database in ubuntu 16.04

Introduction to ArangoDb, open source, NoSQL, multi-model database BigData seems to be getting stronger every day and more and more NoSQL databases are coming out to the market, all trying to position…

How JSON data types work in mysql

First introduced in MySQL 5.7.8, the JSON data type is a specialized binary column type, similar to a blob , but with added machinery to optimize the storage and retrieval…

We use our own and third-party cookies to improve our services, compile statistical information and analyze your browsing habits. This allows us to personalize the content we offer and to show you advertisements related to your preferences. By clicking "Accept all" you agree to the storage of cookies on your device to improve website navigation, analyse traffic and assist our marketing activities. You can also select "System Cookies Only" to accept only the cookies required for the website to function, or you can select the cookies you wish to activate by clicking on "settings".

Accept All Only sistem cookies Configuration