Posted at

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

既存のシステムの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_modeset ステートメントで変更することができる。グローバルに変更する場合は 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_modemy.cnfによる変更

my.cnfsql-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.ymlcommand: ディレクティブに設定する。


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


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