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.
