MySQLのバージョンを変えたらエラーが発生！？ 厳密 SQL モードとは

システム開発をしていて、開発環境では動くが環境を変えると動かないという経験をしたことは無いでしょうか。
環境によって動かないというのは影響も大きく、開発者としてとても心配な現象です。
今回はそんな事象をご紹介したいと思います。

エラー内容

エラーとしては下記のようなエラーが発生していました。

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: ” for column ‘hoge’ at row 1

エラー内容としてはInsert時にIntやBigIntの項目に、空文字を設定した際に発生するエラーです。
テーブル定義やソースが同じローカル環境では動いていたので、MySQLのバージョンの問題かと思いました。
データベースのバージョンにより挙動が変わる事は多々ありますが、今回の原因は違いました。

エラー発生時の環境

エラー発生時の環境は以下のとおりです。

原因

結論としてはMySQLの設定で、無効な値をカラムに挿入した時の挙動についての設定が異なっていました。
具体的な設定の違いとしては、sql_modeに「STRICT_TRANS_TABLES」が設定されていたのが原因でした。

sql_mode(STRICT_TRANS_TABLES)とは

MySQLのリファレンスによると下記となっています。

無効データの制約
MySQLは無効または不適切なデータ値を許容しており、これらをデータエントリに対する有効な値に強制的に変更します。
ただし、厳密SQLモードを有効にして、サーバーが不良値を拒否し、不良値が発生するステートメントを中止するという従来の方法に近い不良値の取り扱いを選択できます。
MySQL5.0.2以降では、STRICT_TRANS_TABLESまたはSTRICT_ALL_TABLES SQL モードを使用して、より厳密な入力値処理を選択できます。

— MySQLリファレンス

簡単にいうと、MySQLでは無効な値をエラーにするか、それなりの値に変換して認めるかを設定出来ます。

sql_modeにSTRICT_TRANS_TABLESやSTRICT_ALL_TABLESを指定すると、数値のカラムに英字など、無効な値の挿入や更新時にエラーを発生させるようになります。

一方、指定しない場合はエラーとせず、数値カラムの場合0などの「最適可能値」を設定するようになります。

最適というと聞こえはいいですが、要は勝手に適当な値に変換されてしまいますので、厳密なシステムを作るためにはSTRICT_TRANS_TABLESを設定したいところです。

sql_modeの確認・修正方法

sql_modeの確認にはいくつかの方法がありますが、簡単に下記のSELECT文を実行することで設定値が確認できます。

SELECT @@GLOBAL.sql_mode;

@@GLOBAL.sql_mode
——————–
STRICT_TRANS_TABLES

下記はデフォルトの設定ですが、STRICT_TRANS_TABLESを外したい場合は、STRICT_TRANS_TABLESを削除してMySQLを再起動します。

sql_mode=NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

XAMMP環境での設定方法

ローカルのXAMMP環境にSTRICT_TRANS_TABLESを設定する場合、XAMMPのバージョンによってはmy.cnfではなくmy.iniになっているようです。

実際に私もローカル環境でsql_modeを変更しましたが、XAMPP5.6.8の場合、my.cnfではなくmy.iniとなっていました。

なお、XAMMPの場合はxampp\mysql\binにmy.iniがあります。

また、デフォルトではsql_modeの記述がありませんが、my.confと同様に追記し、MySQLを再起動すると反映されます。

まとめ

データベースのバージョンアップを行う場合、バージョン毎の違いを確認することはとても重要です。
今回の様に、より厳密にエラーを発生させる設定なども含め、環境を構築する場合は気をつけていきましょう。