MySQL Bugs: #113464: >= 8.0.27 Error 1048 when updating row with empty

Bug #113464	>= 8.0.27 Error 1048 when updating row with empty_sql mode
Submitted:	19 Dec 2023 17:40	Modified:	20 Dec 2023 15:46
Reporter:	Vinicius Malvestio Grippa	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
The problem is described below:

After doing an INSERT on a table with a NOT NULL field, without giving any value to one of such columns, and if this table has an insert trigger, an UPDATE throws error 1048 for MySQL 8.0.35.

It executes fine if you disconnect the session after the insert command and reconnect to do the UPDATE.

NOTES:

- If you remove the trigger, the issue does not happen.
- If you run FLUSH TABLES before the UPDATE, the issue does not happen.
- The trigger is BEFORE INSERT, so, weirdly, an UPDATE operation causes this.
- Reconnecting with the new session and default SQL_MODE does not cause this issue.

How to repeat:
mysql>  set SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO error_1048_test(c1) VALUES ("a");
ERROR 1364 (HY000): Field 'c2' doesn't have a default value
mysql> set SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO error_1048_test(c1) VALUES ("a");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from error_1048_test;
+----+----+
| c1 | c2 |
+----+----+
| a  |    |
+----+----+
1 row in set (0.00 sec)

mysql> UPDATE error_1048_test SET c2 = 'b';
ERROR 1048 (23000): Column 'c2' cannot be null

Hi Mr. Grippa,

Thank you for your bug report.

Our manual explains clearly how that sql mode functions:

"
For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. 
"

However, we repeated your test case and the behaviour contradicts the manual.

That is why we are now verifying this report as a Documentation bug.

However, it might be converted to the code bug, in which case we shall inform you on this page.

Verified as reported.

This started at bug 108155.

Glad this is being looked at.

Hi,

Thanks for checking. I want to clarify the problem is NOT with the INSERT. I understand what is documented about the STRICT_TRANS_TABLES. 

We can validate the implicit conversion in the test and it is correct:

mysql [localhost:48008] {msandbox} (test) >  select * from error_1048_test where c2 is null;
Empty set (0.00 sec)

mysql [localhost:48008] {msandbox} (test) >  select * from error_1048_test where c2 like '';
+----+----+
| c1 | c2 |
+----+----+
| a  |    |
+----+----+
1 row in set (0.00 sec)

The problem is with the UPDATE with a trigger set to BEFORE INSERT.

My UPDATE operation is trying to modify a column and it is respecting the table restrictions of NOT NULL:
 
mysql> UPDATE error_1048_test SET c2 = 'b';
ERROR 1048 (23000): Column 'c2' cannot be null

So, the question is: Why on earth do FLUSH TABLES will allow the same UPDATE operation to run successfully? The sql_mode didn't change:

mysql [localhost:48008] {msandbox} (test) > FLUSH TABLES;
Query OK, 0 rows affected (0.04 sec)

mysql [localhost:48008] {msandbox} (test) >  UPDATE error_1048_test SET c2 = 'b';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

This where the problem resides.

More information:

Triggers with BEFORE UPDATE and BEFORE DELETE works.

Thank you.

We shall enter this info in our internal Bugs DataBase.

I'm setting this as a code bug as it's clearly inconsistent.  It looks like the first error is somehow propagated incorrectly to the failing update.  This is why flush tables and reconnecting 'clears' it.

-- ---------
set session sql_mode = 'strict_trans_tables';
drop table if exists t;
create table t (c1 char(1) not null,c2 char(1) not null) engine=innodb;
create trigger t_bi before insert on t for each row begin end; -- removing this makes update work!
insert into t(c1) values ('a'); -- expected error
set session sql_mode = '';
insert into t(c1) values ('a');
show warnings;                  -- expected warning  
select * from t;
update t set c2 = 'b';          -- not expecting error
show warnings;
flush tables;                   -- why does this change something?
                                -- or instead of flush tables,  reconnect the session and set sql_mode empty.
update t set c2 = 'b';          -- now it works!
show warnings;
select version();

-- ----------------

Output is :
------------
mysql> set session sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t (c1 char(1) not null,c2 char(1) not null) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> create trigger t_bi before insert on t for each row begin end; -- removing this makes update work!
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t(c1) values ('a'); -- expected error
ERROR 1364 (HY000): Field 'c2' doesn't have a default value
mysql>
mysql> set session sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(c1) values ('a');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;                  -- expected warning
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'c2' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> select * from t;
+----+----+
| c1 | c2 |
+----+----+
| a  |    |
+----+----+
1 row in set (0.00 sec)

mysql>
mysql> update t set c2 = 'b';          -- not expecting error
ERROR 1048 (23000): Column 'c2' cannot be null
mysql> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Error | 1048 | Column 'c2' cannot be null |
| Error | 1105 | Unknown error              |
+-------+------+----------------------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> flush tables;                   -- why does this change something?
Query OK, 0 rows affected (0.00 sec)

mysql>                                 -- or instead of flush tables,  reconnect the session and set sql_mode empty.
mysql>
mysql>
mysql> update t set c2 = 'b';          -- now it works!
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> select version();
+-------------+
| version()   |
+-------------+
| 8.4.0-debug |
+-------------+
1 row in set (0.00 sec)

Regards,
Shane

Besides,  5.7.43-enterprise-commercial-advanced
works as expected.

8.0.21 works as expected.  So this is a regression.  Let me find the version it was introduced.

Before 8.0.27 works as expected.  
8.0.27 and newer show the bug.

I don't have time to test it but this looks like a suspect for cause of regression, maybe:

Bug 31719822 - MYSQL CRASH WHEN UPDATING A ROW OF TABLE THAT HAS MULTI VALUE INDEX AND TRIGGER

>Before 8.0.27 works as expected.  
>8.0.27 and newer show the bug.

Thanks for confirming. Can we expect a fix on this in the next release?

Hi,

Next release is in the final test stage, so this will definitely not be fixed there.

We do not know when it will be fixed, but we consider this a serious code bug, which is why we are increasing the severity of the bug.

This bug is the original bug for the following bug report:

https://bugs.mysql.com/bug.php?id=108155