LOAD DATA LOCAL INFILE .. REPLACE INTO ..の闇

LOAD DATA *LOCAL* INFILEはIGNOREキーワードを指定しなくてもエラーをWarningにフォールバックしてくれる。と思っていた。

With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key errors become warnings and the operation continues because the server has no way to stop transmission of the file in the middle of the operation. For duplicate-key errors, this is the same as if IGNORE is specified. IGNORE is explained further later in this section.

Treatment of empty or incorrect field values differs from that just described if the SQL mode is set to a restrictive value. For example, if sql_mode='TRADITIONAL, conversion of an empty value or a value such as 'x' for a numeric column results in an error, not conversion to 0. (With LOCAL, warnings occur rather than errors, even with a restrictive sql_mode value, because the server has no way to stop transmission of the file in the middle of the operation.)

http://dev.mysql.com/doc/refman/5.6/en/load-data.html

( ´-｀).oO（ER_DUP_ENTRYだけをハンドルするようにも見えれば、ER_WARN_TOO_MANY_RECORDSやER_WARN_TOO_FEW_RECORDSもハンドルしてくれるようにも見える。。

ハンドルの仕方の説明も、ErrorではなくWarningにフォールバックするようにも見えれば、IGNOREと同じように *握りつぶした後にWarningだけ出す* ようにも見える。

LOAD DATA INFILE ... REPLACE INTO ... はWarningsがあるとabortする、という仕様だった。意図したものなのかどうかはわからないけど。
— dupont (@dupont_kedama) 2014, 8月 19

そのへんの挙動で毛玉氏がハマっていたようなので、試してみた。

$ echo -e "1\tone" > /tmp/test

mysql56> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.07 sec)

カラムの数が合わないテーブルとtsvファイルを用意。

mysql56> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> TRUNCATE t1;
Query OK, 0 rows affected (0.03 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

STRICTでないsql_modeな場合、両方ともWarningで出力される。

mysql56> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

STRICTなsql_modeでLOAD DATA LOCAL INFILE .. *REPLACE* INTO .. の場合は、エラーになってアボートする。

ざっと読んだ感じ、LOAD DATA LOCAL INFILEの時はエラー(やワーニング)を握りつぶしてから *最後に* その内容をWarningとして出力するのに対し、LOAD DATA LOCAL INFILE .. REPLACE INTO ..のときは最初からWarningとして扱っているので、STRICT_TRANS_TABLESに引っかかってエラーに昇格されている。

318   /* We can't give an error in the middle when using LOCAL files */
 319   if (read_file_from_client && handle_duplicates == DUP_ERROR)
 320     ignore= 1;

REPLACEなしのLOAD DATA LOCAL INFILEの時はここでignore= 1(エラーやワーニングを握りつぶしてから *最後に* その内容をWarningとして出力)をセットする。

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373170, table_list=0x7f217c373200, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_ERROR, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$3 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1"

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373180, table_list=0x7f217c373210, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_REPLACE, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$2 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1"

REPLACEありの場合、handle_duplicates= DUP_REPLACEでこの箇所が呼ばれるため、ignore= 1にならない。

これがドキュメント上のバグ(REPLACEキーワードを指定した場合はエラーハンドルの仕方が更に変わる)なのか、REPLACEキーワードがあった時のエラーハンドルの不備なのか(sql/sql_load.cc:319 の条件文が (read_file_from_client && (handle_duplicates == DUP_ERROR || handle_duplicates == DUP_REPLACE)) でなければならないのか)は知らない。

とりあえずドキュメント上のバグとしてレポートはした。。

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