kamipo traditional (というかSTRICT_ALL_TABLES) では防げないMyISAMという名の化け物

TL;DR

kamipo traditionalですら完全に防ぎきれないアレがあるので、そこを気にするなら出来る限りさっさとMyISAMからInnoDBに引っ越しましょう。


これらの記事を読んだ人向けです。

ルーク！MySQLではkamipo TRADITIONALを使え！ | おそらくはそれさえも平凡な日々

Javaでkamipo traditionalを有効にする - その手の平は尻もつかめるさ

アプリでミスって不正なデータが入るくらいだった500になったほうがマシ。というのが個人的な考えです。

+激しく同意+


さて、激しく同意したところで、kamipo traditionalでは倒せないMyISAMという名の化け物の話をしたいと思います。

kamipo traditionalに対してもMyISAMは果敢に立ち向かう。

その隙間を縫って不正なデータを入れようとしてくるのだッ！

mysql56> CREATE TABLE t1 (num int, val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET SESSION sql_mode='TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> INSERT INTO t1 VALUES (1, 'a'); -- Of course, this succeeds and data is stored.
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t1;
+------+------+
| num  | val  |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)


mysql56> INSERT INTO t1 VALUES (2, 'ab'); -- Of course, this fails and data is not stored.
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t1;
+------+------+
| num  | val  |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

だがkamipo traditional(というかSTRICT_ALL_TABLES)も譲らない。桁の切り詰めをワーニングで許さず、エラーにする。正しい。圧倒的に正しい。

だがMyISAMが攻勢に転じる。出、出～Bulk Insert奴～。

mysql56> INSERT INTO t1 VALUES (3, 'c'), (4, 'dd'), (5, 'e'); -- This returns error, but..?
ERROR 1406 (22001): Data too long for column 'val' at row 2

mysql56> SELECT * FROM t1;
+------+------+
| num  | val  |
+------+------+
|    1 | a    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

(　ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ

(；ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ
_, ._
（；ﾟ Дﾟ）


はい、エラーが返ってるのにデータはINSERTされてます。さすがMyISAM、俺たちにできないことを平然とやってのける。この動作はご存知の方も多いと思います。マニュアルに堂々と書いてあるしね。

非トランザクションテーブルの場合、挿入または更新される最初の行に不適切な値があるとき、どちらのモードでも動作は同じになり、ステートメントが中止されて、テーブルはそのまま変更されません。ステートメントが複数行を挿入または変更し、2 行目以降に不適切な値がある場合、どちらの厳密モードが有効になっているかによって結果は異なります。

STRICT_ALL_TABLES では、MySQL はエラーを返し、残りの行を無視します。ただし、それより前の行が挿入または更新されているため、結果は部分更新となります。これを防ぐには、テーブルを変更することなく中止できる単一行ステートメントを使用します。

STRICT_TRANS_TABLES では、MySQL は無効な値をカラムについてのもっとも近い有効な値に変換し、調整された値を挿入します。値が欠落している場合、MySQL はカラムデータ型の暗黙のデフォルト値を挿入します。いずれの状況でも MySQL はエラーでなく警告を生成し、ステートメントの処理を続行します。暗黙的なデフォルトについては、「データ型デフォルト値」に記載されています。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード


うん。strict指定がなければ丸めてワーニング返すし、STRICT_TRANS_TABLESでもこのケースは丸めて3行入れてワーニングを返す。STRICT_ALL_TABLESでは1行だけ(というか入れられるところまで)入れてエラーを返す。
エラーを返すけど、もちろんロールバックとかされないからデータは残る。これがトランザクション非対応ってことだ。ACIDのAがないとこうなる。「エラーが返らなければそれは成功しているし、エラーが返るならそれは失敗している」という期待を鮮やかに裏切る。さすがMyISAM。

非strictモード, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, どれもいやだ。でも選ばないといけない。嫌だ。

他のトランザクション非対応なストレージエンジンもこんな動作になるし、ストレージエンジン固有のワーニングとかあるとそのハンドルはストレージエンジン側の実装責任になるので、strictなモードと結構相性が悪かったりしそうな予感。。