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!
$ mysql --version
mysql Ver 8.0.21 for Linux on x86_64 (MySQL Community Server - GPL)
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
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.
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
Recommended Posts