文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLのLOAD DATAの罠:その3

続、罠シリーズです。


f:id:treeapps:20180418131549p:plain

環境は、MySQL5.1.45、MacPRO、MacOSX10.6、CPU:Xeon2.8G X 4、MEM:4Gとなります。

今回はまた LOAD DATA について。

LOCALオプション

LOAD DATAには、LOCAL というオプションがあります。

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

www.bunkei-programmer.net

↑の前回の記事で、DuplicateKeyError発生時にエラーが返らなかったのは、LOCALオプションに秘密が有ったためでした。

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

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

なるほど。LOCALの場合は自動的にIGNOREオプションが付くから、エラーにならなかった、というのが真相のようです。

とりあえず実験してみましょう。

LOCALオプション有りでLOAD DATAする

まずは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オプション無しでLOAD DATAする

では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されずrollbackされています!

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

つまり、パスを指定しないと、データベースディレクトリ(/var/lib/mysqlなど)から読み取られてしまうのです。。データベースディレクトリは権限が mysql:mysql なので、その権限を持った状態でSQLコマンドを実行する事は通常ありません。

データディレクトリではなく権限が厳しくない別のディレクトリを参照するとして、

load data infile '/tmp/t_test3.csv' into table t_test3 fields terminated by ',' enclosed by '' lines terminated by '\n';

↑こんな風にパスを書いても動きますが、全てのLOAD DATAに絶対パスを指定する事になってしまい、汎用性を失ってしまいます。

ここで以下のようにLOCALオプションを使うと、絶対パスではなく相対パスで記述する事が可能になります。

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した時でも、自分から自分にファイルが転送されるため多少遅くなる、という事ですね。

MySQLのLOAD DATAの罠:その1 - 文系プログラマによるTIPSブログ
MySQLのLOAD DATAの罠:その2 - 文系プログラマによるTIPSブログ
MySQLのLOAD DATAの罠:その3 - 文系プログラマによるTIPSブログ