MySQL Bugs: #3477: Problem with load data infile

Bug #3477	Problem with load data infile
Submitted:	15 Apr 2004 11:03	Modified:	28 Apr 2004 15:45
Reporter:	David Dobson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.18-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I use load data infile to load data from a text file into a table.  The data in the text file uses tab chars to delimit fields and the \n character at the end of each line.  The data that I am loading has a NULL value in the last field which I specify with \N.  If I do not add a tab character after the \N in the last column, load data infile ignores the last column value.  If I do use the tab after the \N, mysql issues a warning that truncation of the input line occurred, but it does load the last field into the column.

The last column value in the input happens to be going into a timestamp field.

Syntax of the statement I use is:

load data infile "c:/export.txt" into table users;

How to repeat:
See description above.

Suggested fix:
The last column in each line of text should be delimited by either the field delimited or the end of line delimiter.

Are you sure your export.txt file was created with the lines's end with
character \n ?

C:\servers\4.0.18\bin>mysql -uroot bugtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.18-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> LOAD DATA INFILE "c:/export.txt"
    -> INTO TABLE users
    -> FIELDS TERMINATED BY '\t'
    -> LINES TERMINATED BY '\r';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from users;
+------+-------+----------------+
| id   | name  | gtime          |
+------+-------+----------------+
|    1 | mary  | 20040428013209 |
|    2 | jose  | 20040428013209 |
|    3 | peter | 20040428013209 |
+------+-------+----------------+
3 rows in set (0.00 sec)

Yes, I reviewed the code and the character \n is appended to each output line.  I use QT's QTextStream to put the data to the file, so unless QTextStream is converting that character to something else, each line is ended with \n.

I created a file with lines with \n at the end and worked
as expected, then I assume something wrong with your file:

C:\servers\4.0.18\bin>mysql -uroot bugtest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> LOAD DATA INFILE "c:/export.txt"
    -> INTO TABLE users
    -> FIELDS TERMINATED BY '\t'
    -> LINES TERMINATED BY '\n';
Query OK, 4 rows affected (0.25 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 2

mysql> select * from users where id >= 4;
+------+--------+----------------+
| id   | name   | gtime          |
+------+--------+----------------+
|    4 | mary2  | 20040428104106 |
|    5 | jose2  | 20040428104106 |
|    6 | peter2 | 20040428104106 |
+------+--------+----------------+
3 rows in set (0.07 sec)

I still cannot get it to work.  I have 2 timestamp fields at the end of each input record, both have \N as the field value.  The only way that I can get the second timestamp field to have the correct date/time value is to terminate the second timestamp field in the input data with \t, then terminate the line with \n.  

The create table statment is:

CREATE TABLE IF NOT EXISTS users (
   user_id VARCHAR(20) NOT NULL PRIMARY KEY,
   print_system_id INTEGER UNSIGNED NULL,
   first_name VARCHAR(30),
   last_name VARCHAR(30),
   user_id2 VARCHAR(20),
   user_id3 VARCHAR(20),
   password VARCHAR(10),
   user_defined1 VARCHAR(30),
   user_defined2 VARCHAR(30),
   group_id INTEGER UNSIGNED,
   date_updated TIMESTAMP,
   date_added TIMESTAMP,
   UNIQUE INDEX (print_system_id)
);

I have tried different variations of the load data infile statement, specifying the field and line terminators, and all give me the same results.  The date_added field values have all zeros after loading, while the date_updated field has the correct timestamp value.