銀河鉄道~ぽんこつエンジニアのブログ~

思いつきで書いています。

sql_mode=STRICT_TRANS_TABLESにはまった話

MySQL5.6からSQLモードに「STRICT_TRANS_TABLES」がデフォルトで追加された。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 1.7.3.3 無効データの制約

これに関連して、プロシージャーの実行にはまったのでメモ。

環境は以下の通り。
・MySQL5.7
・クライアント:DBeaver
・ドライバ:JDBCドライバ

上記の環境で、DBeaverからストアドプロシージャーを適用し、実行すると「1366 (HY000): Incorrect string value」というエラーが発生。
ストアドプロシージャーの中で、int型変数を空文字('')で初期化しようとしていたことを怒られたのですが、
この現象、以前は発生していませんでした。

以前はと何が違うかというと、クライアントにMySQL workbenchを使用していました。
今回、諸事情により、クライアントをDBeaverに変更したことでこの事象が発生するようになりました。

調査した結果、DBeaverが使用しているJDBCドライバは、デフォルトで「STRICT_TRANS_TABLES」を使用する仕様(バグではなさそう。JDBC準拠?)になっており、
DBeaverでつなぐとセッション変数のsql_modeに強制的に「STRICT_TRANS_TABLES」が追加されていたことがわかりました。

MySQL Bugs: #24526: JDBC connector forces strict mode regardless of server setting
MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.4.4.3 Java 、JDBC および MySQL のタイプ



しかも、もう一つはまった要因として、

「ストアドプロシージャーはCreateされた時のsql_modeで実行される」ということ。
このおかげで、いくらsql_modeを変更してもストアドプロシージャーの動きが変わらずエラーが出続けていました。


対応策として、以下のどちらかで、正常終了するようです。
JDBCドライバの接続のURLでjdbc:mysql://localhost/dbname?jdbcCompliantTruncation=false とする。
②セッション変数のsql_modeから「STRICT_TRANS_TABLES」を外した状態で、Create Procedure を行う。

ちなみにストアドプロシージャーが何のsql_modeに紐づいているかを確認するには、「SHOW CREATE PROCEDURE test1 \G」を実行するとよいです。