| Bug #18335 | LOAD DATA INFILE fixed row bug with CHAR and VARCHAR | ||
|---|---|---|---|
| Submitted: | 18 Mar 2006 19:57 | Modified: | 24 Oct 2007 13:18 |
| Reporter: | Marco Carvalho | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0.22-BK, 5.0.19-nt | OS: | Linux (Linux, Windows XP) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | workaround | ||
[22 Mar 2006 14:10]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.19, and inform about the results.
[22 Mar 2006 22:14]
Marco Carvalho
Test Performed With CHAR,VARCHAR and FLOAT, DECIMAL, DOUBLE
Attachment: PrefomedTest.txt (text/plain), 9.94 KiB.
[22 Mar 2006 22:16]
Marco Carvalho
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-nt
^^^^
See in file section: TestPerformed.txt to see the FLOAT, DECIMAL, DOUBLE bugs.
[3 May 2006 19:24]
Joshua Legler
I had the same problem with data being truncated in varchar columns from a fixed-format text file. The target table's character set was latin1, but I had set the columns in question to utf8. When I changed the columns back to latin1, the LOAD DATA INFILE operation worked correctly. So, perhaps the bug is related to certain character sets or collations. (I was on server version 4.1.14-Max-log, not version 5, but I thought this might be pertinent.)
[22 May 2006 17:03]
Valeriy Kravchuk
Verified on today's build of 5.0.22-BK on Linux. Same results as in your PrefomedTest.txt file uploaded.
[25 Oct 2006 16:13]
Alvin Chin
I have the same problem where I get data truncation or not all data can be imported. I want to import some data in a text file, I've tried playing around with the size of text fields and I even changed the character set to latin9 (ISO) instead of Western European. I'm out of ideas, anyone there can help?
[26 Oct 2006 9:37]
Magnus Blåudd
Please post instructions how to reproduce your problem - either to this bug report or open your own.
[17 Dec 2006 18:42]
Maciej Pilichowski
I could be wrong but it is not necessarily related to fixed-size fields. 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 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. v.5.0.26.
[20 Dec 2006 15:35]
Magnus Blåudd
I think you should create a new bugreport describing your specific problem.
[24 Oct 2007 5:00]
yu qinghe
I think this is a bug before 5.0.45 version,there have not same issue in 5.0.45.
[24 Oct 2007 13:18]
Marco Carvalho
The workaround is loading to a temporary table with varbinary and after insert into RealTable (varcharField) select varbinaryField from temporaryTable; It worked for me :)
Description: CHAR and VARCHAR don't read the field size in column definition for a fixed row LOAD DATA INFILE. MySQL read the fields until they reaches a line terminator cousing the DATA TOO LONG error. BINARY and VARBINARY work fine. How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 40 to server version: 5.0.17-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table text( -> tchar char(5), -> tvarchar varchar(6), -> tbinary binary(4), -> tvarbinary varbinary(7)); Query OK, 0 rows affected (0.09 sec) ---text.txt file--- 5___56____64__47_____7 5aaa56aaaa64aa47aaaaa7 1234512345612341234567 ------------------- mysql> load data infile 'f:/text.txt' -> into table text -> fields terminated by '' -> enclosed by '' -> lines terminated by '\r\n'; ERROR 1406 (22001): Data too long for column 'tchar' at row 1 /* As I already knew, the char type behavior in fixed row import is read the data until a field separetor or a line separetor is reached. Let's try this: */ mysql> load data infile 'f:/text.txt' -> IGNORE into table text -> fields terminated by '' -> enclosed by '' -> lines terminated by '\r\n'; Query OK, 3 rows affected, 12 warnings (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 12 /* Owww.. 12 warnings... let me guess, TRUNCATED and data missing! */ mysql> show warnings; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1265 | Data truncated for column 'tchar' at row 1 | | Warning | 1265 | Data truncated for column 'tvarchar' at row 1 | | Warning | 1261 | Row 1 doesn't contain data for all columns | | Warning | 1261 | Row 1 doesn't contain data for all columns | | Warning | 1265 | Data truncated for column 'tchar' at row 2 | | Warning | 1265 | Data truncated for column 'tvarchar' at row 2 | | Warning | 1261 | Row 2 doesn't contain data for all columns | | Warning | 1261 | Row 2 doesn't contain data for all columns | | Warning | 1265 | Data truncated for column 'tchar' at row 3 | | Warning | 1265 | Data truncated for column 'tvarchar' at row 3 | | Warning | 1261 | Row 3 doesn't contain data for all columns | | Warning | 1261 | Row 3 doesn't contain data for all columns | +---------+------+-----------------------------------------------+ 12 rows in set (0.00 sec) mysql> select * from text; +-------+----------+---------+------------+ | tchar | tvarchar | tbinary | tvarbinary | +-------+----------+---------+------------+ | 5___5 | 7_____ | | | | 5aaa5 | 7aaaaa | | | | 12345 | 123456 | | | +-------+----------+---------+------------+ 3 rows in set (0.00 sec) /* Bad! bad! bad! server..., no donut for you! the LOAD DATA don't read the CHAR size, just like VARCHAR as you can see here: */ mysql> alter table text drop column tchar; Query OK, 3 rows affected (0.30 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from text; Query OK, 3 rows affected (0.05 sec) ---text.txt file--- 6____64__47_____7 6aaaa64aa47aaaaa7 12345612341234567 ------------------- mysql> load data infile 'f:/text.txt' -> into table text -> fields terminated by '' -> enclosed by '' -> lines terminated by '\r\n'; ERROR 1406 (22001): Data too long for column 'tvarchar' at row 1 /* as you see, same problem. Let's try binary types */ mysql> alter table text drop column tvarchar; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 ---text.txt file--- 4__47_____7 4aa47aaaaa7 12341234567 ------------------- mysql> load data infile 'f:/text.txt' -> into table text -> fields terminated by '' -> enclosed by '' -> lines terminated by '\r\n'; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from text; +---------+------------+ | tbinary | tvarbinary | +---------+------------+ | 4__4 | 7_____7 | | 4aa4 | 7aaaaa7 | | 1234 | 1234567 | +---------+------------+ 3 rows in set (0.00 sec) /* God bless them! */ Suggested fix: A wordaraoud is import to binary types in a temporary table then INSERT INTO the real table. In fixed row LOAD DATA we could define size in LOAD DATA clause some how. In floating point have the same problem with FLOAT and DOUBLE. REAL and DECIMAL work fine.