Description:
LOAD DATE failed with "ERROR 1406 (22001)" when the last column of the table type is bit(1).
If you add a non bit(1) type column after the column, LOAD DATA will succeed.
How to repeat:
1. Create a table.
create table test1(
col1 varchar(3) not null default ''
, col2 bit(1) not null default b'0'
)
;
2. Insert some records.
insert into test1 values(
'abc'
, true
);
insert into test1 values(
'abc'
, false
);
3. Export the table data to a file.
mysql --user=user1 db1 --execute "SELECT * FROM test1 INTO OUTFILE '/tmp/test1.txt' CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'"
4. Import the file to the table. (failed)
mysql --user=user1 --default-character-set=utf8mb4 db1 --execute "LOAD DATA INFILE '/tmp/test1.txt' INTO TABLE test1 FIELDS TERMINATED BY '\t';"
ERROR 1406 (22001) at line 1: Data too long for column 'col2' at row 1
5. Add a non bit(1) type column as the last column of the table.
mysql --user=user1 --default-character-set=utf8mb4 db1 --execute "alter table test1 add col3 varchar(1) not null default '';"
6. Export the table data to a file again.
mysql --user=user1 db1 --execute "SELECT * FROM test1 INTO OUTFILE '/tmp/test1-2.txt' CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'"
7. Import the file to the table. (succeeded)
mysql --user=user1 --default-character-set=utf8mb4 db1 --execute "LOAD DATA INFILE '/tmp/test1-2.txt' INTO TABLE test1 FIELDS TERMINATED BY '\t';"
Suggested fix:
Wherever the type bit(1) column is positioned, LOAD DATA should succeed.
