原因

MySQLの起動完了前に、SpringBootが初期接続を試みて失敗していた

環境

• SpringBoot : 2.1.7.RELEASE
• MySQL : 8.0.17
• Docker : 19.03.2
• docker-compose : 1.24.1
• compose-file : 3.7

対応

application.ymlにDB初期接続時のTimeOutの設定を追加

application.yml

spring:
  datasource:
    hikari:
      initializationFailTimeout: 0

上記はコネクションプールライブラリにHikariCPを使用している場合の設定
hikari以降のkeyは使用しているライブラリによって異なる
※コネクションプールの設定は以下の記事を参考にさせて頂きました
Spring Boot DBコネクションプール

initializationFailTimeoutを0に設定するとどうなるか

HikariCPのREADMEによると

If the value is zero (0), HikariCP will attempt to obtain and validate a connection. If a connection is obtained, but fails validation, an exception will be thrown and the pool not started. However, if a connection cannot be obtained, the pool will start, but later efforts to obtain a connection may fail.

値に0を設定した場合、接続を試み、検証を行う。コネクションを獲得したが、検証に失敗した場合、例外を投げ、プールは開始しない。しかしながら、コネクションを獲得できない場合でもプールは開始される。ただし、後からコネクションを獲得しようとしても失敗する可能性がある。

とのこと。

ちなみに

はじめはdocker-compose.ymlでdepends_onを書いておけば、mysqlが利用可能になってからアプリケーションがスタートするものと思っていたが、Docker-docsによるとそうではなく、データベースが起動していない場合の対処はアプリ側で再接続処理を入れるなどして責任を持つべきとのこと

既存のシステムのMySQLを5.6から5.7に変えて動かしたら、group by を使ったSQLでエラーが発生した。

Error Number: 1055

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'dtabase_name.table_name.column_name' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

このエラーは sql_mode システム変数値に起因するもので、 ONLY_FULL_GROUP_BY が設定されている場合に発生する。
このモードの詳しい説明は 5.1.7 サーバー SQL モード に詳しく記載されている。5.6のマニュアルであるが日本語なので理解しやすい。

例えば GROUP BY 句 に指定がないカラムを SELECT 句 の選択リストに指定するとエラーとなる。

SELECT name, address, MAX(age) FROM t GROUP BY name;

この例の場合は address カラムが GROUP BY 句 に指定がないのでエラーとなる。

sql_mode のデフォルト値

• 5.6 >= 5.6.6: NO_ENGINE_SUBSTITUTION
• 5.7: 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

5.7から sql_mode のデフォルト値が変更されたので今回のようなエラーが発生してしまう。

SQLを修正すれば良いが難しいケースもあるので、その場合は sql_mode を変更しよう。

sql_mode の確認方法

MySQLコンソールで以下のように show variables を実行すれば確認できる。

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

しかし、ここで表示されるのはSession変数としてのsql_modeなので、Global変数を確認するには別の方法で確認が必要となる。

-- グローバル変数を確認
mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- セッション変数を確認
mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

sql_mode のコマンドによる変更

sql_mode は set ステートメントで変更することができる。グローバルに変更する場合は global を指定する。

mysql> set global sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@GLOBAL.sql_mode;
+------------------------+
| @@GLOBAL.sql_mode      |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

mysql> select @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

しかし、グローバル変数を変えただけではセッション変数には反映されない。反映させるには切断して再接続する必要がある。
セッション変数を変える場合は session を指定する。（もしくは何も指定しない）

mysql> set session sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

-- または

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.sql_mode;
+------------------------+
| @@session.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

sql_mode の起動パラメーターによる変更

サーバー起動時に --sql-mode="" オプションを使用することで sql_mode を設定できる。

mysqld --sql-mode="NO_ENGINE_SUBSTITUTION"

sql_mode のmy.cnfによる変更

my.cnf に sql-mode="" を指定することで sql_mode を設定できる。

my.cnf

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

DockerでMySQLコンテナを起動する時に sql_mode を設定するには

起動パラメーターで設定する

docker run コマンドでコンテナを起動する場合は、起動コマンドとして --sql-mode="" を与えることで指定が可能となる。

$ docker run --rm -e MYSQL_ROOT_PASSWORD=root  mysql:5.7 --sql-mode=NO_ENGINE_SUBSTITUTION

DockerComposeの場合も同様に起動コマンドに与えることができる。以下のように docker-compose.yml の command: ディレクティブに設定する。

docker-compose.yml

version: '3.7'
services:
  db:
    image: mysql:5.7
    command:
      - --sql-mode=NO_ENGINE_SUBSTITUTION
    environment:
      MYSQL_ROOT_PASSWORD: root

my.cnf で設定する

docker run コマンドで my.cnf をコンテナにわたすにはVolumeとしてマウントする。

docker run --rm -e MYSQL_ROOT_PASSWORD=root -v /path/to/my.cnf:/etc/mysql/conf.d/my.cnf  mysql:5.7

MySQL5.7のコンテナでは /etc/mysql/conf.d/ ディレクトリに任意のファイルを配置することで起動時にロードしてくれる。
また、 MySQLでは複数の my.cnf を順にロードする仕組みがあるのでその中でも順番が遅いファイルにマウントするほうが安全だろう。

my.cnf の読み込み順を確認するには mysql --help | grep 'my.cnf を実行すると確認できる

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

この場合以下の順でロードされるが、/etc/mysql/my.cnf の中で /etc/mysql/conf.d/ をincludeしているので /etc/mysql/conf.d/my.cnf にマウントしてもロードされる。

• /etc/my.cnf
• /etc/mysql/my.cnf
• ~/.my.cnf

DockerComposeの場合は docker-compose.yml の volumes: ディレクティブに設定できる。

docker-compose.yml

version: '3.7'
services:
  db:
    image: mysql:5.7
    volumes:
      - /path/to/my.cnf:/etc/mysql/conf.d/my.cnf
    environment:
      MYSQL_ROOT_PASSWORD: root

開発環境

OS: macOS Mojave 10.14.5
言語: python 3.7.2

エラーの内容

ターミナルでmysqlclientをインストールするために以下のコードを実行しようとした。

$ pip install mysqlclient

エラーが出た。

error: command 'gcc' failed with exit status 1

対処法

$ sudo installer -pkg /Library/Developer/CommandLineTools/Packages/macOS_SDK_headers_for_macOS_10.14.pkg -target /

再度インストール。

$ pip install mysqlclient

  Using cached https://files.pythonhosted.org/packages/4d/38/c5f8bac9c50f3042c8f05615f84206f77f03db79781db841898fde1bb284/mysqlclient-1.4.4.tar.gz
Installing collected packages: mysqlclient
  Running setup.py install for mysqlclient ... done
Successfully installed mysqlclient-1.4.4

解決！

MySQLのwhereでは検索対象がnullの場合無視されてしまうため、以下のようなクエリでは、「DBに入っているfuga_valueの値がnullかつ:paramへの入力がnull」という場合の取得ができません。

select * from hoge where fuga_value = :param;

対処法

NULL安全等価演算子を使って以下のように書けます。

select * from hoge where param <=> :param;

参考

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子

苦戦したのでメモ。

laradockでlaravelの開発環境を構築してた中で、Mysql、phpMyAdminを使おうと思ったのですが、phpMyAdminにログインできず。

環境変数も同じにしたし、Mysqlのバージョンも5.5でインストールしていて、事前につまずく箇所は潰していたのに、それでもログインできず途方にくれていました。

結論

自分のPCの、~/.laradock/data/mysql下のファイルを削除して、改めてdocker-compose up -d nginx mysqlをしたらログインできました。

どうやら、~/.laradock/data/mysqlのファイルが壊れていた模様。

参考になったのはこの記事。

LaradockでMySQLがどうしても立ち上がらない人あつまれー！

解決までの流れ

改めてMysqlを入れ直したり、.envの環境変数が同じになってるか何度も確認したり、それでもダメでひたすらググる。

それでふと、docker-compose psをやって起動しているコンテナ一覧を確認した時に、「あれ？Mysql起動してなくね？」って事に気付く。

それまで、「laradock phpMyadmin」とかのワードでググっててたんですが、mysqlの方に問題あるんじゃね？って調べた結果、上記記事にたどり着く。

まとめ

いやー苦戦しました。多分一日半ぐらい。
冷静に一つ一つ確認してやっていけばすぐにわかったなー、と。

プログラミング歴２ヶ月ぐらいなんですが、また一つ勉強になりました。

ターミナルからmysqlに接続する方法

mysql -u ユーザー名

データベースを一覧で表示する

SHOW DATABASES;

※SQL文の終わりには;(セミコロンが必要)

データベースの作成方法

CREATE DATABASE データベース名;

※SQL文の終わりには;(セミコロンが必要)

データベースを選択する

USE データベース名;

データベースに存在するテーブルを一覧で表示する

SHOW TABLES;

テーブルの作成

CREATE TABLE テーブル名 (カラム名　カラム名の型,　カラム名　カラム名の型,...);

型名	値
INT	数字
VARCHAR(M)	最大M文字の文字列

カラムの追加

ALTER TABLE テーブル名　ADD (カラム名　カラム型, カラム名　カラム型,....);

カラム名の修正

ALTER TABLE テーブル名　CHANGE 古いカラム名　新しいカラム名　新しいカラム型;

※カラム型は変わらなくても必ず記述する必要がある

カラム名の削除

ALTER TABLE テーブル名　DROP カラム名;