MySQL5.7のExtended Supportは２０２３年１０月までです。これから始める場合はMySQL8.0を使いましょう。
既存のシステムの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 を設定できる。
[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: ディレクティブに設定する。
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: ディレクティブに設定できる。
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