お客さんからシステムのサーバー移行を依頼され、サーバー構築した際の話し。
CONTENTS
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
その後、プログラムを実行し動くのを確認。
雑感
今回新しい環境で稼働させる、という優先事項であったため
環境側を変更したが、やはりプログラムを適正になおすべきかなと。
時間と予算の関係は難しい。。。