MySQL

CakePHPで作られた古いシステムをローカル環境で動かしたら何やらエラーが出て動かなくなりました。いろいろ調べたところMySQLのバージョンが上がったことによるエラーだったので、下記にメモ代わりにその症状と対策を書いておきます。

環境

今まで動いていたプログラムの環境は以下の通りです。

  • PHP : 5.3.3
  • CakePHP : 1.3.2
  • MySQL : 5.0.95

これを下記環境で動かしました。

  • PHP : 5.4.45
  • CakePHP : 1.3.2
  • MySQL : 5.7.18

CakePHPはそのまま移植したので同じバージョンです。

症状

このプログラムを新しい環境で動かすと下記のようなエラーが発生しました。

Warning (512): SQL Error: 1055: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘hoge.fuge.field1’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [CORE/cake/libs/model/***.php, line ???]

そのエラーのSQLは以下の通りです。

SELECT `fuge`.`field2`, `fuge`.`field1` FROM `hage` AS `fuge`   WHERE `fuge`.`id` = 9GROUP BY `fuge`.`field2`

見るとグループに「field1」が無いがためにエラーが出ているようです。私の中ではエラーが出て当たり前と思っていたのですが、今までの環境ではこれで動いていたようです。

原因

調べてみたところ、以前のバージョンでは上記のようなグループにカラムが含まれていなくても動いていたようで、最近はSQLのモードに「only_full_group_by」が設定されることによりエラーとなるようです。

対策

さて、原因がわかったところでどのように対策しようかと思ったのですが、短期の仕事なので、MySQLの設定を変更することにしました。

設定を変更するにはmy.cnfに「sql_mode=~」を明記することで対応できるようなのですが、まずはmy.cnfの場所がわからない!滅多に触るものでは無いので「/etc/」にあるだろうとは思っていたのですが、なぜか見つからない。

そこで下記方法で調べてみたところ答えが出ました。

# mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

上記の2行目の先頭に書かれている「/etc/my.cnf」がこれになるようです。

早々に開いてみたところ「sql_mode=~」には何を書けばいいのでしょうか?これも調べてみたところ、まずは現行の設定を調べる必要があるようです。そこでコマンドで

# mysql -u (ユーザー名) -p

とし、パスワードを入力してMySQLのコマンドモードに入ります。そこで

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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に設定されているものは

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

になります。ここから「ONLY_FULL_GROUP_BY」を除いた全てを「/etc/my.cnf」の末尾に

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

と、追記してあげます。後はMySQLをコマンドラインから

# systemctl restart mysqld.service

として、再起動してあげればエラーは消えました。ε-(´∀`*)ホッ

最後に

古い環境で動いていたものを新しい環境で動かすと何かしらエラーが出るものです。PHPならよくあることなんですが、今回のようなMySQLでエラーが出るとは予想もしませんでした。終わってしまえば幸せですが、わかるまでは冷や汗ものです😅

【参考サイト】
MySQL5.7にしたら今まで動いていたSQLがエラーになった #mysql – ジムには乗りたい
my.cnfの場所を調べる – Qiita

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

著者/訳者:奥野 幹也

出版社:技術評論社( 2010-06-12 )

定価:

Amazon価格:¥ 2,957

大型本 ( 520 ページ )

ISBN-10 : 4774142948

ISBN-13 : 9784774142944