お客さんからシステムのサーバー移行を依頼され、サーバー構築した際の話し。
MySQLでdistinctが効かない
DISTINCTとORDER BYのセットでエラーが出力
DBのバージョンをあまり意識せずに構築した結果、
プログラムの一部が動かなくなってしまった。
apacheのエラーファイルに吐き出された内容は以下。
1 | ERROR 3065 (HY 000 ): 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モードを確認。
1 | mysql> SELECT @@GLOBAL.sql_mode; |
2 | +-------------------------------------------------------------------------------------------------------------------------------------------+ |
4 | +-------------------------------------------------------------------------------------------------------------------------------------------+ |
5 | | 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 | |
6 | +-------------------------------------------------------------------------------------------------------------------------------------------+ |
既存サーバー環境でのSQLモードを確認。
1 | mysql> SELECT @@GLOBAL.sql_mode; |
2 | +--------------------------------------------+ |
4 | +--------------------------------------------+ |
6 | +--------------------------------------------+ |
何もない。。。
前任者が意図的に未設定としたのか不明だが、同環境にする必要がある。
エラー原因の特定
今回問題が発生した原因はこれ。
mysqlの5.7からデフォルト値として「ONLY_FULL_GROUP_BY」が追加になったようで、
こちらが原因と考えられる。
指定として正しいしプログラム不備と考えられるが、今回は外してみることに。
MySQLのsql_modeの設定変更
一旦確認してみる
実際にsql_modeの変更で問題ないか、以下を実行して実際のSQLを実行してみる。
問題ないようなので、設定を変更する。
my.cnfでsql_modeを設定
sql_modeを空にするべく、以下を追加
追加が完了したら、mysqlの再起動
1 | /etc/rc.d/init.d/mysqld restart |
その後、プログラムを実行し動くのを確認。
雑感
今回新しい環境で稼働させる、という優先事項であったため
環境側を変更したが、やはりプログラムを適正になおすべきかなと。
時間と予算の関係は難しい。。。