MySQLでdistinctが効かない。mysqlバージョン5.7からの変更。

無断転載禁止 MySQLでdistinctが効かない。mysqlバージョン5.7からの変更。
MySQL

お客さんからシステムのサーバー移行を依頼され、サーバー構築した際の話し。

MySQLでdistinctが効かない

DISTINCTとORDER BYのセットでエラーが出力

DBのバージョンをあまり意識せずに構築した結果、
プログラムの一部が動かなくなってしまった。

apacheのエラーファイルに吐き出された内容は以下。

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'DB名.テーブル名.カラム名' which is not in SELECT list; this is incompatible with DISTINCT

引き継いだプログラムのため、詳細は不明だけど
対象のSQLを確認したところ、確かに若干おかしいところはある。
カラムが合わないDISTINCTして、ORDER BYしてという。。

しかし既存サーバーでは稼働しているのと、プログラムに手を入れるのは影響範囲調査が広がるため、
サーバー環境面で調査することに。

MySQLのモード確認

新サーバー環境でのSQLモードを確認。

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

既存サーバー環境でのSQLモードを確認。

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
|                                            |
+--------------------------------------------+

何もない。。。

前任者が意図的に未設定としたのか不明だが、同環境にする必要がある。

エラー原因の特定

今回問題が発生した原因はこれ。

ONLY_FULL_GROUP_BY

mysqlの5.7からデフォルト値として「ONLY_FULL_GROUP_BY」が追加になったようで、
こちらが原因と考えられる。
指定として正しいしプログラム不備と考えられるが、今回は外してみることに。

MySQLのsql_modeの設定変更

一旦確認してみる

実際にsql_modeの変更で問題ないか、以下を実行して実際のSQLを実行してみる。

SET sql_mode = '';

問題ないようなので、設定を変更する。

my.cnfでsql_modeを設定

sql_modeを空にするべく、以下を追加

vim my.cnf

[mysqld]
sql-mode=""

追加が完了したら、mysqlの再起動

/etc/rc.d/init.d/mysqld restart

その後、プログラムを実行し動くのを確認。

雑感

今回新しい環境で稼働させる、という優先事項であったため
環境側を変更したが、やはりプログラムを適正になおすべきかなと。
時間と予算の関係は難しい。。。

Pocket
LINEで送る