Bug #72568 default values checked before tigger have been executed
Submitted: 8 May 2014 11:14 Modified: 12 May 2014 13:36
Reporter: Jürgen Henge-Ernst Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.73 OS:Any
Assigned to: CPU Architecture:Any
Tags: default value, trigger

[8 May 2014 11:14] Jürgen Henge-Ernst
Description:
If the sqlmode is set to TRADITIONAL it looks like mysql check the values before any triggers are applied. This leads to the following errors for tables or views:

ERROR 1423 (HY000): Field of view 'MV_testTable' underlying table doesn't have a default value
ERROR 1364 (HY000): Field 'Client' doesn't have a default value

Setting the sqlmode to '' all works correctly, but generates warnings.

How to repeat:
CREATE TABLE `MT_testTable` (`PK` int NOT NULL PRIMARY KEY AUTO_INCREMENT, `aText` text NULL, `Client` varchar(81) NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
DELIMITER $$
CREATE TRIGGER `cci_testTable` BEFORE INSERT ON `MT_testTable` FOR EACH ROW BEGIN
  DECLARE ls_connectedUser varchar(81);
  SET ls_connectedUser := SUBSTRING_INDEX(USER(),'@',1);
  IF (NEW.`Client` = '') THEN
    SET NEW.`Client` = ls_connectedUser;
  END IF;
END;
$$
DELIMITER ;
CREATE OR REPLACE VIEW `MV_testTable` AS select `t`.`PK`,`t`.`aText` from `MT_testTable` as `t` WHERE `t`.`Client` = substring_index(user(),'@',1);
SET SESSION sql_mode = 'TRADITIONAL';
INSERT INTO `MV_testTable` (`aText`) VALUES ('a');
SET SESSION sql_mode = '';
INSERT INTO `MV_testTable` (`aText`) VALUES ('b');
SELECT * FROM `MT_testTable`;

Suggested fix:
The values should be checked after all triggers are applied. If a value for a column which is not null is still null the errors should be displayed.
[12 May 2014 13:36] MySQL Verification Team
Hello Jürgen,

Thank you for the bug report.
This is duplicate of Bug #6295, which was fixed in 5.7.1.

This issue is mainly because column constraints were checked before trigger were executed and this behavior has been changed in 5.7.1 onward.

The problem was that if a column is declared as NOT NULL, it wasn't possible to do INSERT NULL (or UPDATE to NULL) even though there was associated trigger, setting NOT-NULL value. Please see http://bugs.mysql.com/bug.php?id=6295

Noted in 5.7.1 changelog.

If a column is declared as NOT NULL, it is not permitted to insert
NULL into the column or update it to NULL. However, this constraint
was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE
trigger) that set the column to a non-NULL value. Now the constraint
is checked at the end of the statement, per the SQL standard.

// I see that 5.7.5 no longer have this issue...

mysql> use test
Database changed
mysql>
mysql> DROP TABLE IF EXISTS MT_testTable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `MT_testTable` (`PK` int NOT NULL PRIMARY KEY AUTO_INCREMENT, `aText` text NULL, `Client` varchar(81) NOT NULL ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
DELIMITER $$
CREATE TRIGGER `cci_testTable` BEFORE INSERT ON `MT_testTable` FOR EACH ROW BEGIN
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER `cci_testTable` BEFORE INSERT ON `MT_testTable` FOR EACH ROW BEGIN
    ->   DECLARE ls_connectedUser varchar(81);
    ->   SET ls_connectedUser = SUBSTRING_INDEX(USER(),'@',1);
    ->   IF NEW.Client IS NULL THEN
    ->     SET NEW.Client = ls_connectedUser;
    ->   END IF;
    -> END;
    -> $$
DELIMITER ;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql>
mysql> DROP VIEW IF EXISTS MV_testTable;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE OR REPLACE VIEW `MV_testTable` AS select `t`.`PK`,`t`.`aText` from `MT_testTable` as `t` WHERE `t`.`Client` = substring_index(user(),'@',1);
SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.01 sec)

mysql> SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `MV_testTable` (`aText`) VALUES ('a');
SET SESSION sql_mode = '';
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `MV_testTable` (`aText`) VALUES ('b');
SELECT * FROM `MT_testTable`;Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SELECT * FROM `MT_testTable`;
+----+-------+--------+
| PK | aText | Client |
+----+-------+--------+
|  1 | a     | root   |
|  2 | b     | root   |
+----+-------+--------+
2 rows in set (0.00 sec)

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)

For MySQL versions <5.7.1 - work around is to set default value non null columns to avoid any problems during "traditional" mode.

Thanks,
Umesh