mysql5.7に上げた時に発生しやすいエラー sql mode

今更ながら・・・mysql5.7のネタですが5.7以下から5.7へアップデートしたときに、それまでのクエリーが動かなくなる事があります。

まず5.7では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

例えば、NO_ZERO_IN_DATEが付くと、時間に’0000-00-00 00:00:00’などの無効な日時では入力できない。

他にも、 ONLY_FULL_GROUP_BY でエラーに可能性がある。
この設定は、group後、取り出すselectが曖昧だとエラー扱いにする設定らしい

例えばこれはNGになる。

select bug_id form bug group by user_id

この場合、グループ化されたときに取り出したいbug_idは、何かを定義していない。
何かが取れればそれでokというだけで書いたmysqlはこれで良かったのだが、ONLY_FULL_GROUP_BYは定義してないと答えを出せないとエラーが出るようになった。

つまり、この例のように、どのbug_idを取得するのかはっきりさせないといけない。
count()やsum()などは答えがあるので大丈夫

select max(bug_id) form bug group by user_id

NO_ZERO_DATEのように、
まだ設定されていない日付等がわかるように‘0000-00-00’を使う時があります。

これらが余計なお世話なので、全て無効にするのも良し、

プログラム以外からの対策

で、治し方がわかっていればソースを修正すればいい話ですが、既存コンテンツ全てを把握して修正することは現実的ではなく難しい場合が多いと思います。
最初から5.7環境で作っていれば問題ないのですけどね。
と、言うことでソースはそのままで動かす方法

1）sql modeからsql modeの設定を必要とわかっているもの以外全部抜いてしまう

2）phpなどからコネクトするときにsql modeを変更する

SET sql_mode='';

memo

生かしておいても良さそうなパラメータ
NO_ENGINE_SUBSTITUTION