Disable Strict Mode for MySQL v8 on Ubuntu

Learn how to disable MySQL Strict Mode for MySQL v8 on Ubuntu 18.04 and Ubuntu 20.04

Ubuntu 20.04 ships with MySQL v8. The installation of MySQL v8 on Ubuntu 20.04 is simple as it gets. But MySQL ships with some default modes enabled which are very strict. This is where the term MySQL strict mode comes from. These modes however don’t go well with some applications. So, we’ll learn how to disable these modes in this tutorial.

The Problem: MySQL Default Modes

We at vpsfix.com don’t usually install MySQL manually. It’s being handled by Virtualmin installation script on Virtualmin Nginx server setup. And time to time, we get requests from customers asking to check why their applications are misbehaving. Following is a screenshot we received recently from one of our clients.

Sendy MySQL 'ONLY_FULL_GROUP_BY' Error

The screenshot is from Sendy Email Marketing application which we setup using our Virtualmin Nginx server setup. Sendy identifies the problem itself and asks to remove ‘ONLY_FULL_GROUP_BY’ from the sql_mode. We were using MySQL v8 in this particular instance, otherwise our instructions to disable strict mode on MySQL 5.7 would work. But MySQL v8 has dropped ‘NO_AUTO_CREATE_USER’ as a MySQL mode and due to this reason that tutorial won’t work for MySQL v8 and above.

The Solution: Disable Strict Mode for MySQL 8

Disabling strict mode for MySQL 8 is very easy. There are multiple ways you can do this. But we’ll be sticking to the same method we used for MySQL 5.7. So, start by login into your VPS as the root user with SSH and create a new file.

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

The modes we include in this file are going to be the only enabled SQL modes for MySQL. You can see a list of SQL modes available here. And the modes not in this file will be disable. So, in this instance we need to make sure that ‘ONLY_FULL_GROUP_BY’ is not there. You may have another requirement. So, adjust the following code as needed and remember to disable only the modes you want to disable.

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

Close the file and restart MySQL,

systemctl restart mysql

Open your application after the restart and any error you had before should be gone now.

Tharindu

Hey!! I'm Tharindu. I'm from Sri Lanka. I'm a part time freelancer and this is my blog where I write about everything I think might be useful to readers. If you read a tutorial here and want to hire me, contact me here.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button

We use cookies to give you the best online experience. By agreeing you accept the use of cookies in accordance with our cookie policy.