mysql の only_full_group_by で引っかかった件

Laravelのテストコードがローカルでは通るけど、gitlabのサーバーに上げると動かなかったので、調べたものです。

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

エラー内容は

this is incompatible with sql_mode=only_full_group_by

取得したデータから年月毎にランダムで1つ取得して、年月の大きい順にソートするために以下のようなSQLを書いてました。

SELECT * 
FROM (
  SELECT *, DATE_FORMAT(created_at, '%Y%m') as month
  FROM table
  ORDER BY RAND()
  ) as table2
GROUP BY month
ORDER BY month desc;

ローカルのmariadbでは問題なく動いてたんですが、gitlabのunittestで落ちてしまって、sql_modeというものがあることに今更ながら気づかされた次第です。 (そもそも環境を合わせろよということはおいておいといてください)

以下のように、 show variables like '%sql_mode%'　と打てば確認できます。

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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

ONLY_FULL_GROUPを使用しない方法は、my.cnfのmysqldの部分に以下を設定
(ONLY_FULL_GROUP_BYを外しました。)

sql_mode=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が指定されている場合は、group by でレコードが一意に識別できない場合、エラーとなる設定のようです。

ただ、Laravelの設定ファイルの config/database.php の設定で、strict => true の場合は、設定が入るらしく、再び失敗しました。

strict => false に変更して、テストは無事通るようになりました。

これでよかったのかはわかりませんが；

以下のような方法あるみたいです。(試した結果カラム1つしか取れなかったですが)

group by してからランダムにひとつずつ抽出する、PostgreSQL & MySQL