MySQL Bugs: #118060: After an error occurs when importing data containing NULL fields, insert can not be executed normally.

Bug #118060	After an error occurs when importing data containing NULL fields, insert can not be executed normally.
Submitted:	25 Apr 2025 16:53	Modified:	7 Feb 20:19
Reporter:	joe Alvin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cannot be nullt, insert error, LOAD DATA INFILE

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.

Thank you for the bug report.
Verified as described.

Workaround: Start a new session before the INSERT operation.

I have investigated this issue and identified the root cause. A patch with a regression test is attached.

Root cause
==========
During LOAD DATA execution, fields may be temporarily marked as nullable
while processing input rows. When LOAD DATA fails (e.g. NULL supplied to
a NOT NULL column under STRICT mode), this temporary NULL (tmp_null) state
on fields is not fully reset.

As a result, the session (THD) keeps a stale field state, and subsequent
statements in the same session may incorrectly treat valid non-NULL values
as NULL, leading to errors such as:

  ERROR 1048 (23000): Column 'b' cannot be null

This explains why the issue reproduces only within the same session, while
a new session works correctly.

Fix
===
Reset the temporary NULL flags on all fields during LOAD DATA cleanup,
including:
  1. The general error exit path
  2. After restoring default records in read_sep_field()

This ensures no temporary field state leaks into subsequent statements.

Test
====
A new MTR test is included to verify:

  1. LOAD DATA fails when NULL is supplied to a NOT NULL column
  2. A subsequent INSERT in the same session succeeds
  3. The inserted row is correctly stored and visible

The fix is limited to cleaning up temporary field state and does not
change LOAD DATA semantics or error handling behavior.

Hi Chuancheng,

Thank you for the contribution.  Before we can accept your patch, you will need to sign the Oracle Contributor Agreement (OCA).  Please, check https://oca.opensource.oracle.com for how to do that.  Once the OCA is signed, use the Contributions tab on the bug report page to upload the patch.

Thanks,
Øystein Grøvlen
MySQL Optimizer Team

I have submitted the OCA and it is currently under review.