Bug #59076 LOAD DATA INFILE needs to handle empty rows better

• Submitted: 21 Dec 2010 3:03
• Reporter: Roel Van de Paar
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.17, 5.1.55
• OS: Any
• CPU Architecture: Any

[21 Dec 2010 3:03] Roel Van de Paar

Description:
Using a import file which has only one row and a blank line, an import gives this (note the 2 rows affected):

mysql> load data infile '/test/import.sql' into table mysql.user fields terminated by ',';
Query OK, 2 rows affected, 38 warnings (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 38

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1261 | Row 2 doesn't contain data for all columns |
[...repeated...]
| Warning | 1261 | Row 2 doesn't contain data for all columns |
+---------+------+--------------------------------------------+
38 rows in set (0.01 sec)

How to repeat:
#1 Create an import.sql file like this:

=========
%,user1,*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0

========= 

   Note the last open line. Make sure it is there in your file also.

#2 load data infile '/path/import.sql' into table mysql.user fields terminated by ',';

[21 Dec 2010 3:10] Roel Van de Paar

mysql> select host,user,password,select_priv from mysql.user where select_priv='';
+------+------+----------+-------------+
| host | user | password | select_priv |
+------+------+----------+-------------+
    |      |          |             |
+------+------+----------+-------------+
1 row in set (0.00 sec)

[21 Dec 2010 3:14] Roel Van de Paar

Also note:

  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

[21 Dec 2010 5:18] MySQL Verification Team

What should mysql do instead?  Reject the row and stop, or skip the malformed line and continue?  Does strict_all_tables sql_mode help?

[21 Dec 2010 13:21] Peter Laursen

I reported similar things here long ago:
http://bugs.mysql.com/bug.php?id=40320

[24 Dec 2010 3:34] Roel Van de Paar

> What should mysql do instead?  Reject the row and stop, or skip the malformed line and continue?

Maybe something like

IF trim($line)=="" THEN
   skip line
ELSE
   IF malformed($line) THEN
      IF no_sort_of_FORCE_option_is_used THEN
        reject row, stop (fail)
      ELSE
        try and import (pretty undefined behavior possible though)
      ENDIF
   ELSE
     import
   ENDIF
ENDIF
      
Where (in "no_sort_of_FORCE_option_is_used") the FORCE option could be something like:

LOAD DATA [FORCE] INFILE ...

[24 Dec 2010 3:42] Roel Van de Paar

mysql> set @@sql_mode="STRICT_ALL_TABLES";
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/test.txt' into table mysql.user fields terminated by ',';
ERROR 1261 (01000): Row 2 doesn't contain data for all columns

So, changing that suggestion above to:

IF trim($line)=="" THEN
   skip line
ENDIF

For a resolution of this bug. (Since the "FORCE" bit is handled already by STRICT_ALL_TABLES).

(Thanks Shane)