MySQL Bugs: #25223: LOAD DATA INFILE -- problems with varchar field

Bug #25223	LOAD DATA INFILE -- problems with varchar field
Submitted:	20 Dec 2006 19:45	Modified:	20 Feb 2007 18:13
Reporter:	Maciej Pilichowski	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Linux (opensuse 10.0)
Assigned to:		CPU Architecture:	Any

Description:
See the bug
http://bugs.mysql.com/bug.php?id=18335

I have
varchar with utf8 encoding and I cannot import a line 
aide-mémoire
Note the French(?) letter. And MySQL says it is too long for varchar(160). The
fields in a file are not fixed-size.

I found this bug report (mentioned above) and I changed my varchar to varbinary. Works like a charm. 

But there is still problem -- the data are imported but I cannot change the
column definition (from varbinary to varchar). Still -- data too long.

How to repeat:
It is sufficient to run the command LOAD DATA INFILE with such entry as given above.

Suggested fix:
For conversion varbinary->varchar is hard to tell. Is it possible to not interpret the values in binary fields? 

But for importing data the "set names" should be respected, so I could import plain ASCII 8bit file into utf8 table.

All in all I think this report has very much in common with the mentioned earlier.

Thank you for a problem report. If you think this report is different enough from bug #18335, please, send complete test case, with CREATE TABLE, data file to load etc. Check it on 5.0.27 and inform about the results.

Ok, how to create table:
create table t
(
  s varchar(200)
) CHARSET = utf8, COLLATE = utf8_polish_ci,ENGINE = InnoDB;

Create text file with this content:
'aide-mémoire'

And import the data:
load data infile '/home/macias/test.txt' into table t fields  enclosed by '\'';

"Set names" does not help.

Sorry, but I was not able to repeat the behaviour described with latest 5.0.36-BK on SuSE Linux:

openxs@suse:~/dbs/5.0> echo 'aide-mémoire' > /tmp/test.txt
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.36 Source distribution

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

mysql> drop table t;
ERROR 1051 (42S02): Unknown table 't'
mysql> create table t
    -> (
    ->   s varchar(200)
    -> ) CHARSET = utf8, COLLATE = utf8_polish_ci,ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> load data infile '/tmp/test.txt' into table t fields  enclosed by '\'';
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+---------------+
| s             |
+---------------+
| aide-mémoire |
+---------------+
1 row in set (0.01 sec)

mysql> show variables like 'char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | latin1                                     |
| character_set_connection | latin1                                     |
| character_set_database   | latin1                                     |
| character_set_filesystem | binary                                     |
| character_set_results    | latin1                                     |
| character_set_server     | latin1                                     |
| character_set_system     | utf8                                       |
| character_sets_dir       | /home/openxs/dbs/5.0/share/mysql/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.00 sec)

> Sorry, but I was not able to repeat the behaviour described with latest
> 5.0.36-BK on SuSE Linux:

What is "5.0.36-BK"? I can see only 5.0.27 for download and 5.1.x beta.