Disable ONLY_FULL_GROUP_BY mysql 8 Ubuntu 20.04 LTS

Question

I want to change the default sql_mode to this:

sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

So I set it up inside etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUT>
sort_buffer_size=10M
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

But when I restart MySQL, I get the following error:

 mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Wed 2021-01-27 16:32:39 EET; 9s ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
    Process: 55142 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, sta>
    Process: 55181 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
   Main PID: 55181 (code=exited, status=1/FAILURE)
     Status: "Server startup in progress"
      Error: 2 (No such file or directory)

I have gone through all of the tutorials on this topic, but it seems that something has changed since those.

I need a permanent solution, because right now, the following setting disappears on every reboot.

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

How can I solve this?

Answer 1

As stated in this answer, the NO_AUTO_CREATE_USER mode was removed in MySQL 8 (assuming that is the version you're using in Ubuntu 20) and will result in an error if included in your config.

Removing it should fix your error. So the line to be added in your cnf file should be:

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