sql_modeのonly_full_group_byを守っていないためにエラーになった件

問題

グラフを表示しようとしたら下記のエラーが表示された。

Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pfc-master_production.posts.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT `posts`.`created_at` FROM `posts` WHERE `posts`.`user_id` = 8 GROUP BY date(created_at)

charts_controller.rb

@dates = dates_calorie.map { |dates| dates.created_at }

エラーログを読み解く

Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and 
Mysql2 :: Error：SELECTリストの式＃1がGROUP BY句になく、

contains nonaggregated column 'pfc-master_production.posts.created_at' 
which is not functionally dependent on columns in GROUP BY clause;
GROUPBY句の列に機能的に依存していない非集計列 
'pfc-master_production.posts.created_at'が含まれています

this is incompatible with sql_mode=only_full_group_by: 
これはsql_mode = only_full_group_byと互換性がありません

SELECT SUM(`posts`.`calorie`) AS sum_calorie, date(created_at) AS date_created_at FROM `posts` WHERE `posts`.`user_id` = 8 GROUP BY date(created_at) ORDER BY created_at DESC

mapメソッドとは？

・mapメソッドを一言で表すと「各要素へ順に処理を実行してくれるメソッド」です。
・要素それぞれにアクセスし、指示した処理を行ってくれる。
【Rails入門】mapメソッドを完全攻略!配列操作の基礎を学ぼう

配列変数.map {|変数名| 具体的な処理 }

今回の場合
@dates = dates_calorie.map(配列変数) { |dates(変数名)| dates.created_at(処理) }

dates_calorieという配列には何が入っているのか？

dates_calorie = current_user.posts.group("date(created_at)").select(:created_at)

ログイン中ユーザーの投稿が1日に複数ある場合、その日ごとでグループ化しcreated_atカラムを取得したものが入っている。
すなわち、1日につき1つのcreated_atカラムが取得され、1日ごとのcreated_atカラム情報たちが配列の中に入っている。

このような情報を取得する理由は、1日ごとの体重を表示するグラフを実装するためだ。
1日に複数の投稿があった時に、グラフに表示するのは1日につき1つだけにしたいためだ。

✖️ [2020-11-09 00:00:00, 2020-11-09 00:11:00, 2020-11-10 00:12:00]
○ [2020-11-09 00:00:00, 2020-11-10 00:12:00, 2020-11-11 00:12:00]

mapメソッドを使ってどのような処理をしているのか？

@dates = dates_calorie.map(配列変数) { |dates(変数名)| dates.created_at(処理) }

上記で説明したとおり、配列dates_calorieには1日ごとのcreated_atたちが配列の中に入っていて、それをmapメソッドを使って配列の中の各要素をdatesという変数で1つずつ取り出し、created_atを取得している。

それらをgonでグラフを表示させるchart.jsに渡してグラフで使うために、インスタンス変数に代入している。

なぜ今回エラーになってしまったのか？

only_full_group_byがわからなかったため、リファレンスでonly_full_group_byについて調べた。

sql_mode = only_full_group_byとは？

GROUP BY 句で名前が指定されていない非集約カラムを、選択リスト、HAVING 条件、または (MySQL 5.6.5 以降で) ORDER リストが参照するクエリーを拒否します。

ONLY_FULL_GROUP_BY が有効な場合、次のクエリーは無効です。1 番目は、選択リスト内の非集約の address カラムが GROUP BY 句で名前を指定されておらず、2 番目は、HAVING 句の max_age が GROUP BY 句で名前を指定されていないため、ともに無効になります。

MySQLリファレンス ONLY_FULL_GROUP_BYとは？

sql_modeとは、MySQLの公式ルールのようなもので、only_full_group_byはMySQL5.6.5以降で新たに設定されたルールらしい。

・only_full_group_byというルールを守っていないためにエラーになったと考えられる。
・GROUP BY句で名前をしていないためエラーになってしまったと考えられる。

GROUP BY句とは？

・「GROUP BY」は、グループ化を行うために使用される命令。
・主に「種類ごとに集計関数を使用する」などといった形で使用するケースが多い。
・例えば「チームごとの人数を調べる」という場合など。
【SQL】GROUP BYで自在に集計!集計関数やHAVINGと合わせて使おう

select [表示する要素名] from [テーブル名] GROUP BY [グループ化する要素名];
SELECT team, COUNT(team) FROM user GROUP BY team;

対策

上記のことから、考えられる対策は2通りだ。

① SQLモードのonly_full_group_byを遵守するため、GROUP BY句を使ったコードに変更する。
② my.cnf(MySQLの設定ファイル)を変更し、only_full_group_byという制約を外す。

今回は②の方法を選択した。
理由は、時間がないためだ。

現在webエンジニアへの転職活動を始めるところで、とにかく一刻も早く企業の採用担当者の方にアプリを見せられる状態にしなくてはならない。
本当は①が望ましいが、今回は時間優先のため②を選択した。

今回の解決方法

my.confの場所を探す
[naota@ip-10-0-0-32 ~]$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

my.confを開く
[naota@ip-10-0-0-32 ~]$ vi /etc/my.cnf

読み込み専用ファイルを上書き保存する時に使う
:w !sudo tee %

my.confに下記の記述を追加した。

my.conf

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

NO_ZERO_IN_DATE 、NO_ZERO_DATEといったsql_modeを調べてみたが、これら自体にonly_full_group_byをOFFにする意味はないとわかった。
おそらく上記の設定でonly_full_group_byにしたのOFFにしたのではなく、only_full_group_byを含んで書いていないから設定されていないのだと考えられる。

→ only_full_group_by追加したら冒頭のエラーが発生したので、この仮説は正しかった。

上記の設定を反映させるためにMySQLを再起動した。

MySQL5.7にアップデートしたらonly_full_group_byでエラーになった

MySQLを停止
[naota@ip-10-0-0-32 ~]$ sudo systemctl stop mysqld.service

MySQLを起動
[naota@ip-10-0-0-32 ~]$ sudo systemctl start mysqld.service

グラフが表示されました！

ソースコード

charts_controller.rbはグラフを表示させるためのコントローラー。

charts_controller.rb

def index
    # カロリー
    @sampleuser = User.find_by(id: 3)
    if user_signed_in?
      # 日付ごとで分けてカロリー合計を算出
      sum_calorie = current_user.posts.group("date(created_at)").sum(:calorie)
      # 日付ごとのカロリー合計がハッシュの形なので値を取得して配列に入れて変数に代入
      array_calorie = sum_calorie.values
    else
      sum_calorie = @sampleuser.posts.group("date(created_at)").sum(:calorie)
      array_calorie = sum_calorie.values
    end
    # gonを使ってデータをjs側に渡す
    gon.data = []
    # mapメソッドで日付ごとのカロリー合計を1つずつ取り出す
    # mapメソッドの使い方 → 配列変数.map {|変数名| 具体的な処理 }
    gon.data = array_calorie.map { |calorie| calorie }

    if user_signed_in?
      # 日付ごとにまとめてそのうちcreated_atカラムだけ取得。配列の形で格納されている
      dates_calorie = current_user.posts.group("date(created_at)").select(:created_at)
    else
      dates_calorie = @sampleuser.posts.group("date(created_at)").select(:created_at)
    end

    gon.date = []
    @dates = dates_calorie.map { |dates| dates.created_at }
    # each文で日付の表記を1つずつ取り出して変える
    @dates.each do |a|
      gon.date << a.strftime("%Y年%m月%d日")
    end


    # 体重
    if user_signed_in?
      gon.weight = current_user.posts.group("date(created_at)").select(:weight).map { |weight| weight[:weight] }
    else
      gon.weight = @sampleuser.posts.group("date(created_at)").select(:weight).map { |weight| weight[:weight] }
    end
  end