MySQL Bugs: #59056: Letters disappearing in mysql CLI (after having using LOAD DATA INFILE)

Bug #59056	Letters disappearing in mysql CLI (after having using LOAD DATA INFILE)
Submitted:	20 Dec 2010 10:14	Modified:	5 Jul 2011 18:21
Reporter:	Roel Van de Paar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0, 5.1, 5.5	OS:	Windows
Assigned to:	Nirbhay Choubey	CPU Architecture:	Any
Tags:	regression

Description:
----- Why are letters disappearing semi-randomly?

C:\>del test

C:\>copy con test
1
2
3
^Z
        1 file(s) copied.

mysql> truncate a;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'C:\\test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
   |
   |
   |
   |
   |
   |
+------+
6 rows in set (0.00 sec)

mysql> truncate a;
Query OK, 0 rows affected (0.01 sec)

C:\>del test

C:\>copy con test
abc
def
ghi
^Z
        1 file(s) copied.

mysql> load data infile '/test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'C:\\test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
 |abc
 |def
 |ghi
 |abc
 |def
 |ghi
+------+
6 rows in set (0.00 sec)

C:\>del test

C:\>copy con test
ab
cd
ef
^Z
        1 file(s) copied.

mysql> truncate a;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile 'C:\\test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile '/test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
  |b
  |d
  |f
  |b
  |d
  |f
+------+
6 rows in set (0.00 sec)

-----

How to repeat:
As per the above. CREATE TABLE statement:

CREATE TABLE `a` (`id` longtext) ENGINE=InnoDB DEFAULT CHARSET=latin1;

secure_file_priv is set to C:\test\ if that matters (see bug #58747)

Suggested fix:
Import correctly at all times

5.5.8 affected as well (same result). 5.0.91 affected as well (slightly different result).  (^Z = F6 in Windows btw)

----- 5.0.91 results 
for the abc | def | ghi test:

mysql> select * from a;
+------+
| id   |
+------+
 | bc
 | ef
 | hi
 | bc
 | ef
 | hi
+------+
6 rows in set (0.00 sec)

For the ab | cd | ef test:

mysql> select * from a;
+------+
| id   |
+------+
  |
  |
  |
  |
  |
  |
+------+
6 rows in set (0.00 sec)
----- 

Because the behavior is different from 5.0.91 I'll tag this as regression, though it's not a regression in the strict sense that functionality has gone backwards (in fact, it has improved :)).

Found that is's a mysql CLI output issue (note "<--------"):

===========
C:\>copy con test
abc
def
ghi
^Z
        1 file(s) copied.

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `a` (`id` longtext) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'C:\\test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
 | bc      <-------- For starters, letters are missing
 | ef
 | hi
 | bc
 | ef
 | hi
+------+
6 rows in set (0.00 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `a` (`textie` longtext) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'C:\\test' into table a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+--------+
| textie |
+--------+
   |       <-------- The only difference here is that the field is not called "id" but "textie")
   |
   |
   |
   |
   |
+--------+
6 rows in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.91-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `a` (`texta` longtext) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/test' into table a;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'C:\\test' into table a;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+-------+
| texta |
+-------+
  | c     <-------- Notice the name of the field is now --1 and the output also returned less
  | f
  | i
  | c
  | f
  | i
+-------+
6 rows in set (0.00 sec)

mysql> select * from a into outfile 'c:/testie';
Query OK, 6 rows affected (0.10 sec)

C:\>type testie;
abc     <-------- Data is there, so it's a mysql CLI bug only
def
ghi
abc
def
ghi
===========

W2 - Unacceptable workaround:

mysql> select * from a;
+------+
| id   |
+------+
  |b
  |d
  |f
  |b
  |d
  |f
+------+
6 rows in set (0.00 sec)

mysql> select * from a\G
*************************** 1. row ***************************
id: ab
*************************** 2. row ***************************
id: cd
*************************** 3. row ***************************
id: ef
*************************** 4. row ***************************
id: ab
*************************** 5. row ***************************
id: cd
*************************** 6. row ***************************
id: ef
6 rows in set (0.00 sec)

mysql> insert into a values ("xy");
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+------+
| id   |
+------+
  |b
  |d
  |f
  |b
  |d
  |f
| xy   |
+------+
7 rows in set (0.00 sec)

So must have something to do with the actual import. Maybe a mixup with \n and \r Windows<>Linux orso.

select hex(id) from a will show what happens, and also check the data file in a hex editor.

You must tell LOAD DATA to use a correct line ending.. So, this should work?

load data infile 'i:/go.txt' into table a lines terminated by 0x0d0a;

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

"Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'."

Shane is correct:

mysql> truncate table a;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/test.txt' into table a;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
  |b
  |d
  |g
+------+
3 rows in set (0.00 sec)

mysql> truncate table a;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/test.txt' into table a LINES TERMINATED BY '\r';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
| ab   |
|
cd  |
|
eg  |
|
    |
+------+
4 rows in set (0.00 sec)

mysql> truncate table a;
Query OK, 0 rows affected (0.01 sec)

mysql> load data infile '/test.txt' into table a LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
| ab   |
| cd   |
| eg   |
+------+
3 rows in set (0.00 sec)

mysql> truncate table a;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/test.txt' into table a LINES TERMINATED BY 0x0d0a;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from a;
+------+
| id   |
+------+
| ab   |
| cd   |
| eg   |
+------+
3 rows in set (0.00 sec)

shouldn't this be marked as 'not a bug' since the last comment shows everything works as expected?

Discussed with Nirbhay on IRC previously. Don't close. More to follow later.