Description:
After an error occurs when importing data containing NULL fields, when attempting to insert normal fields, it reports that fields cannot be null.
mysql> select * from test111.t2 order by id desc limit 1;
+-----+-----+----+--------------+
| id | a | b | c |
+-----+-----+----+--------------+
| 500 | 101 | tt | {"abc": "c"} |
+-----+-----+----+--------------+
1 row in set (0.00 sec)
mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}');
ERROR 1048 (23000): Column 'b' cannot be null
How to repeat:
step1:
sql_mode is STRICT_TRANS_TABLES
step2:
CREATE TABLE `t2` (
`id` int NOT NULL,
`a` char(20) NOT NULL DEFAULT '',
`b` char(20) NOT NULL DEFAULT '',
`c` json NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
step3:
get load data.
[root@localhost 20250425]# cat -A t2.dat
1^C"101"^CNULL^C"{"adf":3}"$
2^C"102"^CNULL^C"{"adf":3}"$
3^C"103"^CNULL^C"{"adf":3}”$
please attention : ^C is 0x03
step4:
At session1 execute load data and it will report error.
mysql> load data infile '/home/mysql8/20250425/t2.dat' into table test111.t2 fields terminated by x'03' optionally enclosed by '\"' (id,a,b,c);
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'b' at row 1
step5:
At session1, execute insert and it is error
mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}');
ERROR 1048 (23000): Column 'b' cannot be null
step6:
At session2,execute insert and it is successfully
mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}');
Query OK, 1 row affected (0.03 sec)
step7:
At session1 ,execute select and it is the newly inserted content
mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}');
ERROR 1048 (23000): Column 'b' cannot be null
mysql> select * from test111.t2 order by id desc limit 1;
+-----+-----+----+------------+
| id | a | b | c |
+-----+-----+----+------------+
| 501 | abc | bb | {"abc": 2} |
+-----+-----+----+------------+
1 row in set (0.00 sec)
Suggested fix:
suggest1: Please confirm the "thd->check_for_truncated_fields = CHECK_FIELD_WARN;” in sql_load.cc and it is effect on current THD. But when the THD has been used by other thread ,it may be some wrong.
suggest2: Please confirm the “m_check_for_truncated_fields_saved = current_thd->check_for_truncated_fields;” in field.cc .it is you wanted ,or not.
© 2025, Oracle Corporation and/or its affiliates
Description: After an error occurs when importing data containing NULL fields, when attempting to insert normal fields, it reports that fields cannot be null. mysql> select * from test111.t2 order by id desc limit 1; +-----+-----+----+--------------+ | id | a | b | c | +-----+-----+----+--------------+ | 500 | 101 | tt | {"abc": "c"} | +-----+-----+----+--------------+ 1 row in set (0.00 sec) mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}'); ERROR 1048 (23000): Column 'b' cannot be null How to repeat: step1: sql_mode is STRICT_TRANS_TABLES step2: CREATE TABLE `t2` ( `id` int NOT NULL, `a` char(20) NOT NULL DEFAULT '', `b` char(20) NOT NULL DEFAULT '', `c` json NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 step3: get load data. [root@localhost 20250425]# cat -A t2.dat 1^C"101"^CNULL^C"{"adf":3}"$ 2^C"102"^CNULL^C"{"adf":3}"$ 3^C"103"^CNULL^C"{"adf":3}”$ please attention : ^C is 0x03 step4: At session1 execute load data and it will report error. mysql> load data infile '/home/mysql8/20250425/t2.dat' into table test111.t2 fields terminated by x'03' optionally enclosed by '\"' (id,a,b,c); ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'b' at row 1 step5: At session1, execute insert and it is error mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}'); ERROR 1048 (23000): Column 'b' cannot be null step6: At session2,execute insert and it is successfully mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}'); Query OK, 1 row affected (0.03 sec) step7: At session1 ,execute select and it is the newly inserted content mysql> insert into test111.t2 values(501,'abc','bb','{"abc":2}'); ERROR 1048 (23000): Column 'b' cannot be null mysql> select * from test111.t2 order by id desc limit 1; +-----+-----+----+------------+ | id | a | b | c | +-----+-----+----+------------+ | 501 | abc | bb | {"abc": 2} | +-----+-----+----+------------+ 1 row in set (0.00 sec) Suggested fix: suggest1: Please confirm the "thd->check_for_truncated_fields = CHECK_FIELD_WARN;” in sql_load.cc and it is effect on current THD. But when the THD has been used by other thread ,it may be some wrong. suggest2: Please confirm the “m_check_for_truncated_fields_saved = current_thd->check_for_truncated_fields;” in field.cc .it is you wanted ,or not.