MySQL Bugs: #117256: Repeating uncaused error "Column cannot be null" after single error on table with trigger

Bug #117256	Repeating uncaused error "Column cannot be null" after single error on table with trigger
Submitted:	21 Jan 13:19	Modified:	28 Jan 18:19
Reporter:	Mike Amromin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.4.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	not null collumn, trigger

Description:
I caught an uncaused error in my production application when upgrading from MySQL 5.6 to MySQL 8.4 (v8.4.4).
I started receiving the error "Column cannot be null" after some time of application work, although the value I tried to update was not null.

How to repeat:
To reproduce the problem use sessional database client like MySQL Workbench:

1) Create two tables with empty trigger (it may contain any logic, but no matter) and fill test data:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (id INT, table2_id INT, value INT NOT NULL);

CREATE TRIGGER trigger_after_update AFTER UPDATE ON table1 FOR EACH ROW BEGIN /*any logic*/ END;

DROP TABLE IF EXISTS table2;
CREATE TABLE table2 (id INT);

INSERT INTO table1 (id, table2_id, value) VALUES (1, 1, 0);
INSERT INTO table1 (id, table2_id, value) VALUES (1, NULL, 0);
INSERT INTO table2 (id) VALUES (1);

2) Update table1 with expected legal error "Column 'value' cannot be null":
UPDATE table1 a INNER JOIN table2 b ON a.table2_id=b.id
SET a.value=NULL
WHERE b.id=1;

3) Update table1 with unexpected error "Column 'value' cannot be null" which repeat at any further attempts in this database session (in other session all will work)
UPDATE table1 a INNER JOIN table2 b ON a.table2_id=b.id
SET a.value=1
WHERE b.id=1;

You can try any other way to change table1 in this database session:
UPDATE table1 a SET a.value=1 WHERE a.id=1;
or just
INSERT INTO table1 (id, table2_id, value) VALUES (2, 2, 2);
You will receive an uncaused error "Column 'value' cannot be null".

Suggested fix:
The error "Column 'value' cannot be null" mast not be fired if the updated column value is not null.

Hi,

I need help reproducing this. If I understand you correctly you need to create this table and data and trigger on 5.6 and upgrade to 8.4.4 or you just make this on 8.4.4. I tried both and did not reproduce the problem. Can you clarify the procedure? Thanks.

This behaviour is reproducing on clear version 8.4.4 MySQL Community Server - GPL

My steps to reproduce:
1) Run MySQL Workbench
2) Create a schema with name "test" and set it as default
3) Create a new SQL tab and execute preparing actions:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (id INT, table2_id INT, value INT NOT NULL);
CREATE TRIGGER trigger_after_update AFTER UPDATE ON table1 FOR EACH ROW BEGIN /*any logic*/ END;
DROP TABLE IF EXISTS table2;
CREATE TABLE table2 (id INT);
INSERT INTO table1 (id, table2_id, value) VALUES (1, 1, 0);
INSERT INTO table1 (id, table2_id, value) VALUES (1, NULL, 0);
INSERT INTO table2 (id) VALUES (1);
4) In the same SQL tab clear previous script and execute:
UPDATE table1 a INNER JOIN table2 b ON a.table2_id=b.id SET a.value=NULL WHERE b.id=1;
The result is an expected error "Column 'value' cannot be null"
5) In the same SQL tab clear previous command and execute:
UPDATE table1 a INNER JOIN table2 b ON a.table2_id=b.id SET a.value=1 WHERE b.id=1;
The result is an unexpected error "Column 'value' cannot be null" because updated value is not null.

Verified as described. Thanks for the report and test case.