treeのメモ帳

■[mysql]LOAD DATAの罠：その3

続、罠シリーズ。

環境：MySQL5.1.45、MacPRO、MacOSX10.6、CPU:Xeon2.8G X 4、MEM:4G

今回はまた LOAD DATA について。
LOAD DATAには、LOCAL というオプションがある。

この LOCAL オプション使用時のエラーについて、知っていないと解らない動作が有った。

http://d.hatena.ne.jp/treeapps/20110328/1301330472
この件でDuplicateKeyError発生時にエラーが返らなかったのは、LOCALオプションに秘密が有った。

IGNORE が指定されている場合、ユニークキー値が既存のレコードの値と重複する入力レコードがスキップされます。 どちらのオプションも指定されていない場合、動作は LOCAL キーワードが指定されているかどうかによって異なります。 LOCAL が指定されていない場合、重複したキーの値が検出されるとエラーになり、テキストファイルの残りの部分が無視されます。 LOCAL が指定されている場合、デフォルトの動作は IGNORE が指定されている場合と同じです。なぜなら、サーバは処理の最中にファイルの送信を停止することができないためです。

http://dev.mysql.com/doc/refman/4.1/ja/load-data.html

なるほど。LOCALの場合は自動的にIGNOREオプションが付くから、エラーにならなかったのか。
とりあえず実験してみよう。
まずはLOCALオプションを指定したバージョン。

$ mysql -uroot test -e "desc t_test3"
 +-------+-------------+------+-----+---------+-------+
 | Field | Type        | Null | Key | Default | Extra |
 +-------+-------------+------+-----+---------+-------+
 | id    | int(11)     | NO   | PRI | NULL    |       |
 | name  | varchar(20) | YES  |     | NULL    |       |
 +-------+-------------+------+-----+---------+-------+
$ cat ./t_test3.csv 
1,あああ
1,あああ
$ cat ./loadDataInfile.sh 
#!/bin/sh
mysql -uroot test<<EOF
set autocommit=0;
load data local infile '/tmp/t_test3.csv' into table t_test3 fields terminated by ',' enclosed by '' lines terminated by '\n';
commit;
EOF
if [ $? -eq 1 ]; then
    echo "error"
else
    echo "success"
fi
$ ./loadDataInfile.sh 
success
$ mysql -uroot test -e "select * from t_test3"
+----+-----------+
 | id | name      |
 +----+-----------+
 |  1 | あああ    |
 +----+-----------+

やっぱりDuplicateKeyErrorも起きずsuccessが返り、強制コミットされてる。

ではLOCALオプション無しで試してみる。csvは同様のものを使用。

$ cat ./loadDataInfile.sh 
#!/bin/sh
mysql -uroot test<<EOF
set autocommit=0;
load data infile '/tmp/t_test3.csv' into table t_test3 fields terminated by ',' enclosed by '' lines terminated by '\n';
commit;
EOF
if [ $? -eq 1 ]; then
    echo "error"
else
    echo "success"
fi
$ ./loadDataInfile.sh 
ERROR 1062 (23000) at line 2: Duplicate entry '1' for key 'PRIMARY' error
$ mysql -uroot test -e "select * from t_test3"
$

キター！　ちゃんとerrorが返ったし、commitされてない！
これ結局LOCAL指定でエラーを返してrollbackしてくれる動作にはできないのかなあ。

なんでこんなにLOCALに拘るかというと、csvの配置場所を指定しないといけないから。。

ファイルがサーバホスト上にある場合、サーバは次の規則に従います。
絶対パス名が指定されている場合、サーバはそのパス名をそのまま使用する。
相対パス名が指定されている場合、サーバはサーバのデータディレクトリを基準にしてファイルを検索する。
ディレクトリの指定なしにファイル名が指定されている場合、サーバはカレントデータベースのデータベースディレクトリでファイルを検索する。
注意: これらの規則に基づき、./myfile.txt という名前のファイルはサーバのデータディレクトリから読み取られるのに対し、myfile.txt という名前の同じファイルについてはカレントデータベースのデータベースディレクトリから読み取られます。たとえば、次の LOAD DATA ステートメントでは、db2 データベース内のテーブルへのファイルのロードが明示的に指定されていますが、ファイル data.txt はカレントデータベースである db1 のデータベースディレクトリから読み取られます。

http://dev.mysql.com/doc/refman/4.1/ja/load-data.html

つまり、パスを指定しないと、データベースディレクトリから読み取られる。。データベースディレクトリは権限が mysql:mysql なので物にならないかと。
データディレクトリではなく別のディレクトリを参照するとして、
load data infile '/tmp/t_test3.csv' into table t_test3 fields terminated by ',' enclosed by '' lines terminated by '\n';
↑こんな風にパスを書いてしまったら、全てのLOAD DATAにパスを指定しないといけない。
で、localを使うとこれが解決できる。
$ cd /tmp
続いて↓を実行
load data local infile 't_test3.csv' into table t_test3 fields terminated by ',' enclosed by '' lines terminated by '\n';
こう書ける。

まあヒアドキュメント使えば、LOCAL指定なしで、パスも変数使えるんですけどね。

$ cat ./loadDataInfile.sh 
#!/bin/sh
DATA_DIR=/tmp/
mysql -uroot test<<EOF
set autocommit=0;
load data infile "${DATA_DIR}t_test3.csv" into table t_test3 fields terminated by ',' enclosed by '' lines terminated by '\n';
commit;
EOF
if [ $? -eq 1 ]; then
    echo "error"
else
    echo "success"
fi
$ ./loadDataInfile.sh 
ERROR 1062 (23000) at line 2: Duplicate entry '1' for key 'PRIMARY'
error

ちなみにLOCAL指定は、指定無しのときよりパフォーマンスが悪い。

LOAD DATA INFILE ステートメントは、テキストファイルからテーブルにレコードを高速で読み取ります。LOCAL キーワードが指定されている場合、このキーワードは接続のクライアント側に関連して解釈されます。LOCAL が指定されていると、クライアントホスト上のクライアントプログラムによってファイルが読み取られ、サーバに送られます。LOCAL が指定されていない場合、ファイルはサーバホスト上に存在しなければならず、サーバによって直接読み取られなければなりません（LOCAL は MySQL バージョン 3.22.6 以降で使用できます）。

http://dev.mysql.com/doc/refman/4.1/ja/load-data.html

つまり、DBサーバにシェルスクリプトを配置して、自鯖から自鯖に対してLOCALオプションでLOAD DATAした時でも、
自分から自分にファイルが転送されるため、多少遅くなるのだ。

ツイートする

Permalink | コメント(0) | トラックバック(2) | 15:55