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)
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)