| Bug #305 | LOAD DATA for fixed-row input | ||
|---|---|---|---|
| Submitted: | 17 Apr 2003 15:35 | Modified: | 16 May 2003 3:28 |
| Reporter: | Ray Rodriguez | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 4.1.0-alpha (all?) | OS: | Any (all) |
| Assigned to: | CPU Architecture: | Any | |
[18 Apr 2003 4:35]
MySQL Verification Team
For the import of fixed length data, only field enclosure / termination strings have to be set at zero length. Lines termination, on the other hand, has to be specified explicitely, or otherwise a default will be used.
[18 Apr 2003 6:22]
Ray Rodriguez
if LINES TERMINATED BY '' is not used in addition to FIELDS TERMINATED BY '' and FIELDS ENCLOSED BY '' for fixed-row import then fixed-row import DOES NOT WORK (only 1 record is imported). The documentation only mentions the need for FIELDS TERMINATED BY '' and FIELDS ENCLOSED BY '', this is incorrect. Try it. My input file was 124293 records, in case that make a difference.
[16 May 2003 3:28]
Michael Widenius
The current manual says: If you don't specify a LINES clause, the default is the same as if you had written this: LINES TERMINATED BY '\n' In other words, if you only set FIELDS ENCLOSED BY "" TERMINATED BY "", then LINE TERMINATED BY is still set to "\n" and MySQL will assume the text file consist of lines with fixed length fields, where lines are terminated with \n This means that if you don't have any \n in the file, then MySQL will only read one row. This is described at: http://www.mysql.com/doc/en/LOAD_DATA.html (I did recenely add some more documentation to clarify this issue)
Description: LOAD DATA is documented as accepting fixed-row length input by specifying FIELDS TERMINATED BY and FIELDS ENCLOSED BY values as both empty (''), but this results in only 1 record being imported. From online manual: If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) format is used. With fixed-row format, no delimiters are used between fields. Instead, column values are written and read using the ``display'' widths of the columns. For example, if a column is declared as INT(7), values for the column are written using 7-character fields. On input, values for the column are obtained by reading 7 characters. Fixed-row format also affects handling of NULL values; see below. Note that fixed-size format will not work if you are using a multi-byte character set. How to repeat: Import data from a fixed-length file using a statement such as: load data local infile '/appl/rates/coverage.dat' into table coverage FIELDS ENCLOSED BY '' TERMINATED BY ''; results in only 1 row of data being imported. Suggested fix: use: load data local infile '/appl/rates/coverage.dat' into table coverage FIELDS ENCLOSED BY '' TERMINATED BY '' ; Note the addition of LINES TERMINATED BY '' to the statement, this results in the entire file being imported, perhaps this problem occurs because the default value for LINES TERMINATED BY is '\n'?