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.
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.
