[mysql]load data infile の localオプションが曲者

■環境

fedora17
mysql5.1(mroonga)

MySQLには SQLモード という概念があり、これによって動作が変わる。
デフォルトでは http://d.hatena.ne.jp/sakaik/20100225/mysqlautochange のような動作。

で、数値型に文字列つっこもうとしたらエラーにしてほしい、なんてときは
http://dev.mysql.com/doc/refman/5.1/ja/server-sql-mode.html
で、TRADITIONAL モードにしてしまえばエラーになる。

これはODBCドライバとかJDBCドライバとかつかってると、クライアントによって動作が変わるかもしれないので、mysql コマンドでSQLを叩いて確認するのがいい。

で、LOAD DATA INFILE と LOAD DATA LOCAL INFILE では SQLモードを指定したとしても動作が異なる。
マジでハマった。

http://forums.mysql.com/read.php?79,378105,378105

sql-mode="strict_all_tables" has no effect when using Load Data Infile

上記リンク、LOAD DATA INFILE では sql_mode の strinct_all_tables が効果ないって困っている。


実際のSQLは示されていないけれど、これは LOAD DATA LOCAL INFILE の動作っぽい。


2個目にレスしている人は、

It's working for me. Try it in CAPS: 

set sql_mode='STRICT_ALL_TABLES'; 

うまく言ったよ、っと言っている。


この認識の際は「LOCAL」があるかないかだと思う。


実際に試してみたけれども、LOAD DATA LOCAL INFILE を実行しているセッションでは SET SESSION sql_mode='TRADITIONAL' を指定しても効果がなかった。

一応、そのセッション中で SELECT @@session.sql_mode; で STRICT_ALL_TABLES があることを確認した。

一方で、mysql コマンドから同じ INSERT 文を発行するとエラーになり、 show warnings; するとWARNING ではなく ERROR と、明確に差が出た。

で、これを解決するには下記。

http://bugs.mysql.com/bug.php?id=52084

LOAD DATA INFILE にすると sql_mode の設定が有効になって、型違いのデータをつっこもうとしたらエラーになってくれた。

LOCAL があるかないかですごい動作が変わるみたいです。。。