Bug #85805 Incorrect ER_BAD_NULL_ERROR after LOAD DATA LOCAL INFILE
Submitted: 5 Apr 2017 10:50 Modified: 5 Apr 2017 11:15
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.17, 8.0.1 OS:CentOS (7.3)
Assigned to: CPU Architecture:Any
Tags: cannot be null, ER_BAD_NULL_ERROR, ERROR 1048, table cache

[5 Apr 2017 10:50] tsubasa tanaka
Description:
After LOAD DATA LOCAL INFILE with "\N"(NULL) value into NOT NULL column, simple INSERT statement gets to return ER_BAD_NULL_ERROR even in doesn't have NULL in the statement.

How to repeat:
$ echo -e "1\t\\N" > /tmp/test.tsv

mysql> CREATE DATABASE d1;
mysql> CREATE TABLE d1.t1 (num int, val varchar(32) NOT NULL);
mysql> LOAD DATA LOCAL INFILE '/tmp/test.tsv' INTO TABLE d1.t1;
mysql> SELECT * FROM d1.t1;

mysql> INSERT INTO t1 VALUES (2, 'dummy');
ERROR 1048 (23000): Column 'val' cannot be null

Suggested fix:
LOAD DATA LOCAL INFILE sets the "m_is_tmp_null" flag (This maybe correct because trying to insert NULL into NOT NULL column)

Breakpoint 1, Field::set_tmp_null (this=this@entry=0x7f96ac1e25e0) at /home/yoku0825/mysql-5.7.17/sql/field.cc:1024
1024    {
(gdb) bt
+bt
#0  Field::set_tmp_null (this=this@entry=0x7f96ac1e25e0) at /home/yoku0825/mysql-5.7.17/sql/field.cc:1024
#1  0x00000000007cbcac in Field::set_null (this=this@entry=0x7f96ac1e25e0, row_offset=row_offset@entry=0)
    at /home/yoku0825/mysql-5.7.17/sql/field.cc:1651
#2  0x0000000000e0d1f4 in read_sep_field (skip_lines=0, enclosed=<optimized out>, read_info=
          @0x7f96e01bb4a0: {file = -1, buffer = 0x7f96ac1aeb90 "N", end_of_buff = 0x7f96ac1aec1b "", buff_length = 139, <snip>

But the statement never turns off the flag, so INSERT statement after the LOAD DATA LOCAL INFILE statement returns ER_BAD_NULL_ERROR until the Field structure(table-cache) flushes.

-- Attaching gdb

(gdb) b Field::check_constraints
+b Field::check_constraints
Breakpoint 1 at 0x7d1e50: file /home/yoku0825/mysql-5.7.17/sql/field.cc, line 1588.
(gdb) c
+c
Continuing.

-- Execute INSERT statement in another terminal.

<1st-breakpoint hits for "num" column>

Breakpoint 1, Field::check_constraints (this=0x7f96ac1b4c68, mysql_errno=mysql_errno@entry=1048)
    at /home/yoku0825/mysql-5.7.17/sql/field.cc:1588
1588    {
(gdb) c
+c
Continuing.

<2nd time breakpoint will be check for "val" column>

Breakpoint 1, Field::check_constraints (this=0x7f96ac1b1990, mysql_errno=mysql_errno@entry=1048)
    at /home/yoku0825/mysql-5.7.17/sql/field.cc:1588
1588    {
(gdb) p *this
+p *this
$1 = {
  <Proto_field> = {
    _vptr.Proto_field = 0x1c966b0 <vtable for Field_varstring+16>
  },
  members of Field:
  ptr = 0x7f96ac1b1885 "\005dummy",
  m_null_ptr = 0x0,
  m_is_tmp_nullable = false,
  m_is_tmp_null = true,
  m_count_cuted_fields_saved = CHECK_FIELD_WARN,
  table = 0x7f96ac40b3b0,
  orig_table = 0x7f96ac40b3b0,
  table_name = 0x7f96ac40b4a0,
  field_name = 0x7f96ac26e245 "val",
  comment = {
    str = 0x1517970 "",
    length = 0
  },
  key_start = {
    map = 0
  },
  part_of_key = {
    map = 0
  },
  part_of_key_not_clustered = {
    map = 0
  },
  part_of_sortkey = {
    map = 0
  },
  unireg_check = Field::NONE,
  field_length = 128,
  flags = 4097,
  field_index = 1,
  null_bit = 0 '\000',
  is_created_from_null_item = false,
  m_indexed = false,
  m_warnings_pushed = 0,
  gcol_info = 0x0,
  stored_in_db = true
}

"m_is_tmp_null = true" causes ER_BAD_NULL_ERROR.

https://github.com/mysql/mysql-server/blob/mysql-5.7.17/sql/field.cc#L1587-L1634

Workaround, renew a table-cache or reconnect a client-session(reconnection makes a new table-cache)

mysql80> INSERT INTO t1 VALUES (2, 'dummy');
ERROR 1048 (23000): Column 'val' cannot be null

mysql80> FLUSH TABLES;
Query OK, 0 rows affected (0.15 sec)

mysql80> INSERT INTO t1 VALUES (2, 'dummy');
Query OK, 1 row affected (0.00 sec)

mysql80> SELECT * FROM d1.t1;
+------+-------+
| num  | val   |
+------+-------+
|    1 |       |
|    2 | dummy |
+------+-------+
2 rows in set (0.00 sec)
[5 Apr 2017 11:15] Miguel Solorzano
Thank you for the bug report.