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.

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

Recommended Posts

My.cnf configuration problem that I was addicted to when I was touching MySQL 8.0 like 5.7
A memo that I was addicted to when making batch processing with Spring Boot
A story I was addicted to when getting a key that was automatically tried on MyBatis
What I was addicted to when introducing the JNI library
What I fixed when updating to Spring Boot 1.5.12 ・ What I was addicted to
The story I was addicted to when setting up STS
I was addicted to starting sbt
A note when I was addicted to converting Ubuntu on WSL1 to WSL2
What I was addicted to when implementing google authentication with rails
About the matter that I was addicted to how to use hashmap
[Rails] How to solve ActiveSupport :: MessageVerifier :: InvalidSignature that I was addicted to when introducing twitter login [ActiveStorage]
I was addicted to rewriting to @SpringApplicationConfiguration-> @SpringBootTest
I was addicted to the roll method
I was addicted to the Spring-Batch test
Memorandum: What I was addicted to when I hit the accounting freee API
[Rails] I was addicted to the nginx settings when using Action Cable.
Problems I was addicted to when building the digdag environment with docker
[CircleCI] I was addicted to the automatic test of CircleCI (rails + mysql) [Memo]
I was addicted to using RXTX on Sierra
I was addicted to installing Ruby/Tk on MacOS
I was addicted to doing onActivityResult () with DialogFragment
What I was addicted to when developing a Spring Boot application with VS Code
The story that did not disappear when I tried to delete mysql on ubuntu
A site that was easy to understand when I was a beginner when I started learning Spring Boot
What I was addicted to when trying to properly openAPI/Swagger documentation with Rails + Grape + Grape Swagger
I was addicted to the NoSuchMethodError in Cloud Endpoints
I was addicted to the record of the associated model
How to batch initialize arrays in Java that I didn't know when I was a beginner
I was addicted to WSl when trying to build an android application development environment with Vue.js
What I was addicted to while using rspec on rails
I was addicted to setting default_url_options with Rails devise introduction
I was addicted to looping the Update statement on MyBatis
A story I was addicted to in Rails validation settings
I was addicted to the setting of laradock + VSCode + xdebug
What I was addicted to with the Redmine REST API
I was addicted to using Java's Stream API in Scala
[Circle CI] A story I was addicted to at Start Building
I was addicted to the API version min23 setting of registerTorchCallback
I want to notice that I forgot to specify arg when building Docker
I was able to deploy the Docker + laravel + MySQL app to Heroku!
A story that I was addicted to twice with the automatic startup setting of Tomcat 8 on CentOS 8