MySQL 5.7 のONLY_FULL_GROUP_BY が出た時にDockerでやった対処

**MySQL5.7のExtended Supportは２０２３年１０月までです。これから始める場合はMySQL8.0を使いましょう。**

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

```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 モード](https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html) に詳しく記載されている。5.6のマニュアルであるが日本語なので理解しやすい。

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

```sql
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` を実行すれば確認できる。

```sql
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変数を確認するには別の方法で確認が必要となる。

```sql
-- グローバル変数を確認
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` を指定する。

```sql
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` を指定する。（もしくは何も指定しない）

```sql
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` を設定できる。

```bash
mysqld --sql-mode="NO_ENGINE_SUBSTITUTION"
```

# `sql_mode` の`my.cnf`による変更

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

```config:my.cnf
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
```

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

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

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

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

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

```yaml: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` を実行すると確認できる

```bash
$  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:` ディレクティブに設定できる。

```yaml: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
```