Bug #84077 | CURRENT_TIMESTAMP produces zeros in TRIGGER | ||
---|---|---|---|
Submitted: | 6 Dec 2016 13:19 | Modified: | 19 May 2017 18:49 |
Reporter: | Power Gamer | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 5.7.11 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | current_timestamp |
[6 Dec 2016 13:19]
Power Gamer
[6 Dec 2016 15:06]
MySQL Verification Team
Hi! I have repeated your behavior with the latest 5.7: mysql> CREATE TABLE tbl( -> a TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -> b INT NOT NULL DEFAULT '123' -> ); Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE tbl2( -> c TIMESTAMP NOT NULL -> ); Query OK, 0 rows affected (0.93 sec) mysql> mysql> DELIMITER $ mysql> CREATE TRIGGER tbl_before_insert BEFORE INSERT ON tbl FOR EACH ROW -> BEGIN -> SET @a = NEW.a; -> SET @b = NEW.b; -> INSERT INTO tbl2(c) VALUES(NEW.a); -> END;$ Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> INSERT INTO tbl VALUES (); Query OK, 1 row affected (0.00 sec) mysql> SELECT @a, @b; +---------------------+------+ | @a | @b | +---------------------+------+ | 0000-00-00 00:00:00 | 123 | +---------------------+------+ 1 row in set (0.00 sec) mysql> select * from tbl; +---------------------+-----+ | a | b | +---------------------+-----+ | 2016-12-06 15:53:02 | 123 | +---------------------+-----+ 1 row in set (0.00 sec) mysql> select * from tbl2; +---------------------+ | c | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec) This is a verified bug now. Thank you.
[19 May 2017 18:49]
Paul DuBois
Posted by developer: Noted in 5.7.19, 8.0.2 changelogs. For a table having a TIMESTAMP or DATETIME column having a default of CURRENT_TIMESTAMP, the column could be initialized to 0000-00-00 00:00:00' if the table had a BEFORE INSERT trigger.
[23 Dec 2017 15:43]
Maximilian Walter
For what reason was this bug report closed? Granted, there is a mention in the changelogs, but is this really expected behaviour and not a bug?
[2 Jan 2018 9:52]
Nisha Padmini Gopalakrishnan
Posted by developer: Hello Maximilian, It was indeed a bug which was fixed in 5.7.19. The fix ensures that the function default current_timestamp for the table would be evaluated before processing the 'BEFORE INSERT' or 'BEFORE UPDATE' triggers defined on it. Thus the appropriate current timestamp is generated. Since it was fixed, the bug report was closed. Regards, Nisha