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: | |
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
[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