MySQL Bugs: #114208: table update fails with an "Column xxxx cannot be null" error.

Bug #114208	table update fails with an "Column xxxx cannot be null" error.
Submitted:	4 Mar 15:41	Modified:	30 Jul 15:13
Reporter:	Wen Liang Chang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	8.0.31, 8.0.32, 8.0.33, 8.0.34, 8.0.36	OS:	Windows (10, 11)
Assigned to:		CPU Architecture:	Any

Description:
table design will all columns type not null, Global and Session sql_mode="".
Running an update SQL script on existing data on "column_a" will prompt "Column column_b cannot be null" error. this causes the inability to run SQL update script using MySQL Workbench's latest version.

issue started from Version 8.0.31 till the latest.

The workaround is to install MySQL Workbench Version 8.0.30

How to repeat:
Environment:

AWS Aurora MySQL Serverless V1 [version 5.7.12-log(MySQL Community Server(GPL)]
Global sql_mode="".

table1 with all columns defined int NOT NULL, varchar NOT NULL, ...

running SQL update script like:

UPDATE `table1` `t1`
JOIN `table2` `t2` ON `t1`.`id`=`t2`.`t1_id`
SET `t1`.`column_a`="value"
WHERE 1;

error prompt stated that the other column cannot be null.

no issue on MySQL Workbench Version 8.0.30

Hello Wen Liang Chang,

Thank you for the bug report.
Could you please provide create table and insert statements for table1 and table2?
Also, upgrade to latest version. Thanks.

Regards,
Ashwini Patil

sorry that not able to provide the actual create table and insert statements query.

the finding is MacOS versions MySQL workbench did not have this issue.
AWS Aurora MySQL Serverless V1 deprecated soon. My company was migrating the database to MySQL 8 and probably will not face this issue anymore.

Thanks.

HI Mr. Chang,

Let us know if you have problems after your migration.

If you get the same error, just create a new bug report.