Bug #95643 LOAD DATA failed when the last column of the table type is bit(1)
Submitted: 5 Jun 3:44 Modified: 5 Jun 12:45
Reporter: Satoru MATSUO Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:8.0/5.7/5.6 OS:Any
Assigned to: CPU Architecture:Any

[5 Jun 3:44] Satoru MATSUO
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.
[5 Jun 8:31] Satoru MATSUO
Typo corrected.
[5 Jun 8:36] Satoru MATSUO
"LOAD DATE" in the Description section is typo. It should have been "LOAD DATA". The same typo in the Synopsis has been corrected.
[5 Jun 12:45] Miguel Solorzano
Thank you for the bug report.