Bug #206 Intermittent column display problem

• Submitted: 28 Mar 2003 8:16
• Modified: 31 Mar 2003 12:23
• Reporter: [ name withheld ]
• Status: Closed
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 4.0.12
• OS: Any (Any)
• CPU Architecture: Any

[28 Mar 2003 8:16] [ name withheld ]

Description:
There seems to be an intermittent column display problem in mySQL Client 4.0.12-max-nt on Windows 2000.  

On a multi-column select, the first column is displayed incorrectly, cutting off characters.

However, repeating the same select into outfile will export the correct output.

How to repeat:
The original data was imported from a text file:

VADER	Wilmington	Yes
WALLACE	Wilmington	Yes
WBESGP01	Wilmington	No
WBESGP02	Wilmington	No
WBESGT01	Wilmington	No
WBESYP06	Wilmington	No
WBESYP07	Wilmington	No
WEDNESDAY	Wilmington	Yes
WUSUWPHDEVZB329	Wilmington	No

mysql> create table orig (
    ->  host    varchar(30) not null,
    ->  site    varchar(30),
    ->  isbak   varchar(10)
    -> );
Query OK, 0 rows affected (0.26 sec)

mysql> load data infile 'test.txt'
    ->  into table orig;
Query OK, 978 rows affected (0.05 sec)
Records: 978  Deleted: 0  Skipped: 0  Warnings: 0

The host field is displayed fine here:

mysql> select host from orig;
...
| VADER           |
| WALLACE         |
| WBESGP01        |
| WBESGP02        |
| WBESGT01        |
| WBESYP06        |
| WBESYP07        |
| WEDNESDAY       |
| WUSUWPHDEVZB329 |
+-----------------+
978 rows in set (0.05 sec)

But here the first field is displayed incorrectly, cutting off characters.  This will similarly happen to the first field if two columns are selected:

mysql> select * from orig;
...
     |R           | Wilmington          | Yes
     |ACE         | Wilmington          | Yes
      |P01        | Wilmington          | No
      |P02        | Wilmington          | No
      |T01        | Wilmington          | No
      |P06        | Wilmington          | No
      |P07        | Wilmington          | No
     |ESDAY       | Wilmington          | Yes
| WUSUWPHDEVZB329 | Wilmington          | No       |
+-----------------+---------------------+----------+
978 rows in set (0.06 sec)

Suggested fix:
Using select into outfile will export the correct output.

[28 Mar 2003 9:07] [ name withheld ]

A workaround is to begin the select statements with:
select ' ', 

Since the problem affects the first column only.

[31 Mar 2003 9:59] MySQL Verification Team

The behavior reported I found when the source file is
mixing spaces and tabs character between columns. 
Using a consistent formatted file the behavior was
as expected:
 
mysql> load data infile 'test.txt'" into table orig fields
    -> terminated by ";";
Query OK, 21 rows affected (0.00 sec)
Records: 21  Deleted: 0  Skipped: 0  Warnings: 20

mysql> select * from orig;
+-----------------+------------+-------+
| host            | site       | isbak |
+-----------------+------------+-------+
| VADER           | Wilmington | Yes   |
| WALLACE         | Wilmington | Yes   |
| WUSUWPHDEVZB329 | Wilmington | No    |
| WBESGP01        | Wilmington | No    |
| WBESGP02        | Wilmington | No    |
| WBESGP03        | Wilmington | No    |
| WBESGP04        | Wilmington | No    |
| VADER           | Wilmington | Yes   |
| WALLACE         | Wilmington | Yes   |
| WUSUWPHDEVZB329 | Wilmington | No    |
| WBESGP01        | Wilmington | No    |
| WBESGP02        | Wilmington | No    |
| WBESGP03        | Wilmington | No    |
| WBESGP04        | Wilmington | No    |
| VADER           | Wilmington | Yes   |
| WALLACE         | Wilmington | Yes   |
| WUSUWPHDEVZB329 | Wilmington | No    |
| WBESGP01        | Wilmington | No    |
| WBESGP02        | Wilmington | No    |
| WBESGP03        | Wilmington | No    |
| WBESGP04        | Wilmington | No    |
+-----------------+------------+-------+
21 rows in set (0.00 sec)

[31 Mar 2003 12:23] MySQL Verification Team

For to complete the last post, in your case the command
line should be:

load data infile 'test.txt' into table orig lines terminated by "\r\n";