Bug #59076 LOAD DATA INFILE needs to handle empty rows better
Submitted: 21 Dec 2010 3:03
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.17, 5.1.55 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[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] Shane Bester
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)