My.cnf configuration problem that I was addicted to when I was touching MySQL 8.0 like 5.7

Overview

スクリーンショット 2020-10-22 12.39.42.png

I'm developing a Rails app here, but if you're developing a web app, you'll know if you get this error.

this is incompatible with sql_mode=only_full_group_by

The details of the problem are explained in various articles, so please read the following articles. This time, ** I was addicted to touching MySQL 8.0 with the feeling of 5.7 **.

-I got an error with only_full_group_by after updating to MySQL 5.7 «MySQL «Technical Blog «Pronet Co., Ltd. -MySQL sql_mode is set correctly? – Engineering is in!

environment

$ mysql --version
mysql  Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)

phenomenon

The settings of mysqld are the contents set by referring to the above article. Then the following error occurred.

$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
$ sudo vi /etc/my.cnf

---
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
---

$ systemctl restart mysqld.service
Failed to restart mysqld.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files

$ sudo systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code)since tree 2020-10-22 03:50:33 UTC; 27s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 8909 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 8885 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 8909 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 2 (No such file or directory)
October 22 03:50:32 xxx-xxx-xxx-xxx.yyyyy.zzz systemd[1]: Starting MySQL Server...
October 22 03:50:33 xxx-xxx-xxx-xxx.yyyyy.zzz systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
October 22 03:50:33 xxx-xxx-xxx-xxx.yyyyy.zzz systemd[1]: Failed to start MySQL Server.
October 22 03:50:33 xxx-xxx-xxx-xxx.yyyyy.zzz systemd[1]: Unit mysqld.service entered failed state.
October 22 03:50:33 xxx-xxx-xxx-xxx.yyyyy.zzz systemd[1]: mysqld.service failed.

$ sudo less /var/log/mysqld.log
...
2020-10-22T03:50:33.416784Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2020-10-22T03:50:33.416979Z 0 [ERROR] [MY-010119] [Server] Aborting

Cause

The MySQL I was using was 8.0 and it didn't make any sense. It was a problem caused by using it with a 5.7 feeling. Sweat In MySQL 8.0, the contents of sql_mode have been partially changed from 5.7, and this time I was addicted to the fact that NO_AUTO_CREATE_USER was removed.

MySQL :: MySQL 5.7 Reference Manual :: 5.1.7 Server System Variables
- Default Value
  - ONLY_FULL_GROUP_BY / STRICT_TRANS_TABLES / NO_ZERO_IN_DATE / NO_ZERO_DATE / ERROR_FOR_DIVISION_BY_ZERO / NO_AUTO_CREATE_USER / NO_ENGINE_SUBSTITUTION
MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables
- Default Value
  - ONLY_FULL_GROUP_BY / STRICT_TRANS_TABLES / NO_ZERO_IN_DATE / NO_ZERO_DATE / ERROR_FOR_DIVISION_BY_ZERO / NO_ENGINE_SUBSTITUTION

Coping

With the following settings with NO_AUTO_CREATE_USER removed, MySQL started successfully and the summary error was resolved.

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