MySQL Bugs: #80885: Not null update fails with an "Column cannot be null" error.

Bug #80885	Not null update fails with an "Column cannot be null" error.
Submitted:	29 Mar 2016 12:59	Modified:	1 Apr 2016 7:27
Reporter:	David Wolf	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.7.11	OS:	Debian (Debian GNU/Linux 8 (jessie))
Assigned to:		CPU Architecture:	Any

Description:
After migrating to MySQL 5.7.11 (from 5.5.47), we see update statements with not null values fail with an "Column cannot be null" error that have previously passed:

mysql> UPDATE `tbl_b` b, `tbl_a` a SET b.`value` = 'VAL' WHERE a.`id` = b.`tbl_a_id` AND a.`usage` = 'U' AND a.`key` = 'K';
Column 'value' cannot be null

The update in the test scenario in the "How to repeat" section passes if one of the following changes are made:

* The "UNIQUE KEY `usage`" in tbl_a is removed.
* The (empty) trigger on tbl_b is dropped.
* The update statement is changed so that it wont reference tbl_a.
* The update statement is executed in a different session than the inserts.

How to repeat:
CREATE TABLE `tbl_a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `usage` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `usage` (`usage`,`key`)
) ENGINE=InnoDB;

CREATE TABLE `tbl_b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` mediumtext NOT NULL,
  `tbl_a_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TRIGGER beforeInsertTblB BEFORE INSERT ON tbl_b
FOR EACH ROW
BEGIN
END;

INSERT INTO `tbl_a` (`id`, `usage`, `key`) VALUES (1, 'U', 'K');

INSERT INTO `tbl_b` (`tbl_a_id`) VALUES (1);

UPDATE `tbl_b` b, `tbl_a` a SET b.`value` = 'VAL' WHERE a.`id` = b.`tbl_a_id` AND a.`usage` = 'U' AND a.`key` = 'K';

Hello David,

Thank you for the report.
This is an expected behavior and hence not a bug. Since 5.7.7 and up default sql_mode is strict. Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition - please refer https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

In your case, issue should be in the INSERT statement itself, `value` is declared NOT NULL and you are trying to insert while excluding the column which defined as not null

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

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `tbl_a` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `key` varchar(255) NOT NULL,
    ->   `usage` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `usage` (`usage`,`key`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> CREATE TABLE `tbl_b` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `value` mediumtext NOT NULL,
    ->   `tbl_a_id` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TRIGGER beforeInsertTblB BEFORE INSERT ON tbl_b
    -> FOR EACH ROW
    -> BEGIN
    -> END;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tbl_a` (`id`, `usage`, `key`) VALUES (1, 'U', 'K');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tbl_b` (`tbl_a_id`) VALUES (1);
ERROR 1364 (HY000): Field 'value' doesn't have a default value
mysql>

Thank you for your interest in MySQL.

Thanks,
Umesh

My apologies for not including this important information in my report, but we already switched the sql_mode in order to get back to the previous behaviour:

mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 rows in set (0.93 sec)

So the insert passes with only a warning, and the row is written with value being an empty string:

mysql> INSERT INTO `tbl_a` (`id`, `usage`, `key`) VALUES (1, 'U', 'K');
Query OK, 1 rows affected (0.06 sec)

mysql> INSERT INTO `tbl_b` (`tbl_a_id`) VALUES (1);
Query OK, 1 rows affected, 1 warnings (0.10 sec)

mysql> UPDATE `tbl_b` b, `tbl_a` a SET b.`value` = 'VAL' WHERE a.`id` = b.`tbl_a_id` AND a.`usage` = 'U' AND a.`key` = 'K';
Column 'value' cannot be null

Thank you for the feedback!

Observed this with 5.7.11 build( 5.6.29/5.5.50 not affected)