"BOKU"のITな日常

BOKUが勉強したり、考えたことを頭の整理を兼ねてまとめてます。

MariaDBのバージョンアップで動いていたSQLがエラーになるようになった

f:id:arakan_no_boku:20211207220619p:plain

目次

MariaDBのバージョンアップで動いていたSQLがエラーになるようになった

MariaDBの10.1で動いていたシステムで、MariaDBをバージョンアップしたら、SQLエラーが頻発するようになったと連絡がありました。

MariaDB10.1は1年以上前の「End of Life」に到達してるバージョンで、かつ、AmazonRDSなんかでも、もう10.2以降しかサポートされていないので、バージョンアップ自体は絶対にしないといけないとのことです。

mariadb.org

 

原因はMariaDBsql_modeのデフォルトが変わったこと

原因はすぐにわかりました。

SQL_MODEです。

今まで、SQL_MODEで指定していなかった「STRICT_TRANS_TABLES」がデフォルトで有効になったのです。

MariaDBSQL_MODEのデフォルトは以下のように変わってきています。

f:id:arakan_no_boku:20211212005230p:plain

それで。

10.1の時はデフォルトで設定されていなかった「STRICT_TRANS_TABLES」が有効になったので、今まではMariaDBが自動的に調整して警告扱いになっていた「長すぎる文字列」や「範囲外の数値」を渡すようなミスがあるSQL文がエラーになったのです。

とりあえず、SQLの潜在バグが表面化しているだけなので、地道にSQLを直してくださいということで話は終わりました。

やっぱ、最初にゆるゆるで開発して楽をしたらダメですね。

あとで、きっちりシッペ返しが来ます。

もっとも、相当、大変だったみたいに聞いていたのですが、よくよく裏話を聞いたら、iniファイルでSQL_MODEを指定して「STRICT_TRANS_TABLES」を無効化して逃げた・・ようするに問題先送り・・にしたらしいですけど(笑)。

 

MariaDBsql_modeの主要なものを整理

今回問題になった「sql_mode」はSQL文の評価のしかたを指定するものです。

SQLエラーとするか自動補正してスルーするかとか、特定の構文を許すかどうかみたいなことがこれで決まります。

mariadb.com

以下に、よく目にするものだけ、抜粋で書いておきます。

 

sql_mode:STRICT_TRANS_TABLES

今回問題になったものです。

トランザクションのテーブルを変更するSQLに対して、STRICT_MODEを適用します。

設定しない場合、MariaDBは、長すぎる文字列の切り捨てや、範囲外の数値の調整など、無効な値を自動的に調整して警告のみ行いますが、STRICT_MODEの場合は、これを厳密にエラーにします。

 

sql_mode:ERROR_FOR_DIVISION_BY_ZERO

ゼロ除算をエラーにするよという指定。指定しない場合は、ゼロ除算のときにエラーにしないでnullを返します。

 

sql_mode:NO_AUTO_CREATE_USER

認証情報の指定なしに、GRANTが自動的にユーザを作成しないようにする・・ようするにちゃんと、CREATE USERしてから、GRANTで権限を与えるという当たり前の手順をちゃんと踏めよ・・とう話です。。

 

sql_mode:NO_ENGINE_SUBSTITUTION

テーブルの作成時に指定されたストレージエンジンが利用できない場合にエラーとする・・これも当たり前だけど、指定しないと勝手にデフォルトのストレージエンジンを使うように変更されてしまいます。

 

sql_mode:PIPES_AS_CONCAT

パイプ文字を文字列連結演算子として使用できるようになります。

つまり、CONCAT("A", "B")の代わりに、"A" || "B "のようにかけるようになります。

ORACLEとかDB2とかでは書けるみたいなので、追加でこれを設定しているプロジェクトは多いみたいですね。

 

sql_mode:ANSI_QUOTES

文字列を囲む引用符には単一引用符(')だけを使用できる=二重引用符(")は文字列を囲む引用符として利用できなくなります。つまり、'と" を文字列の引用文字として混在しているシステムで、これを指定するとアプリケーションがぶっ壊れるリスクがあるというやつです。ようするに、SQLで文字列を囲む引用符は単一引用符だけを使うように、普段からルール化して、最初からこいつを設定しといたほうがよさげです。

 

sql_mode:IGNORE_SPACE

関数名と'('の間にスペース(タブ文字や改行文字を含む)を入れることができるようになり、組み込み関数が予約語としてチェックされるようにもなります。

これも追加で設定しているプロジェクトは多いのかな。

個人的な経験からですけど。

こんな感じです。

ほかにもいっぱいありますが、とりあえずこんなところで。

ではでは。