Clearly, I don't want the result to be what it currently is:
id
t1
t2
1
2022-05-04 13:57:45.000
1970-01-01 01:02:10.000
It's so easy to achieve this behaviour if I wanted it by explicitly specifying the default. It's so hard to remember that this feature exists, having spent yet another 30 minutes debugging my own code until I was reminded of this: https://mariadb.com/kb/en/timestamp/#automatic-values
It's very random, why would the first TIMESTAMP ever be special? Why not the last? Or the fifth? What about DATETIME, why doesn't it apply there?
I strongly suggest you remove this "feature," which looks like a bug to me every time I run into it, and it's really not the first time. Users can very easily specify DEFAULT expressions themselves
Attachments
Issue Links
duplicates
MDEV-28632Change default of explicit_defaults_for_timestamp to ON
In what sane world would anyone expect t1 and t2 to have different values because of the inconsistent ON UPDATE CURRENT
Mr Niall Litchfield
added a comment - - edited Agreed, consider also
CREATE TABLE t2 (
id INTEGER NOT NULL ,
t1 TIMESTAMP NOT NULL ,
t2 TIMESTAMP NOT NULL ,
t3 TIMESTAMP
CONSTRAINT pk_t PRIMARY KEY (id)
);
INSERT INTO t (id, t1, t2)
VALUES (1, timestamp '1970-01-01 01:00:38.0' , timestamp '1970-01-01 01:00:38.0' );
UPDATE t
SET
t.t3 = timestamp '1970-01-01 01:02:10.0' ;
SELECT * FROM t;
In what sane world would anyone expect t1 and t2 to have different values because of the inconsistent ON UPDATE CURRENT
This TIMESTAMP behavior existed in MariaDB and before it in MySQL since at least 1997. Almost every application that uses MariaDB has tables with TIMESTAMP fields.
We simply cannot remove it without breaking a huge number of applications for a huge number of users.
Sergei Golubchik
added a comment - This TIMESTAMP behavior existed in MariaDB and before it in MySQL since at least 1997. Almost every application that uses MariaDB has tables with TIMESTAMP fields.
We simply cannot remove it without breaking a huge number of applications for a huge number of users.
I understand it's a big change, but MySQL 8 has done it, too: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9bd50fd444283f7ffaa2feda3ebdaf5b. I haven't investigated MySQL's stance on backwards compatibility. I think a lot of features can be maintained by specifying some flags in your installation, to keep existing systems running like before? But for new systems, I don't really see the value of this feature, given that the ON UPDATE and DEFAULT clauses can be specified explicitly when this is desired.
Lukas Eder
added a comment - I understand it's a big change, but MySQL 8 has done it, too: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9bd50fd444283f7ffaa2feda3ebdaf5b . I haven't investigated MySQL's stance on backwards compatibility. I think a lot of features can be maintained by specifying some flags in your installation, to keep existing systems running like before? But for new systems, I don't really see the value of this feature, given that the ON UPDATE and DEFAULT clauses can be specified explicitly when this is desired.
a lot of features can be maintained by specifying some flags in your installation, to keep existing systems running like before?
And there already is an option to control this behaviour (explicit_defaults_for_timestamp=ON|OFF).
Alejandro Duarte
added a comment - a lot of features can be maintained by specifying some flags in your installation, to keep existing systems running like before?
And there already is an option to control this behaviour (explicit_defaults_for_timestamp=ON|OFF).
I am changing this from Bug to Task as the current behavior is documented and used that way by many users. Changing the default of explicit_defaults_for_timestamp is not a bug fix.
Ralf Gebhardt
added a comment - I am changing this from Bug to Task as the current behavior is documented and used that way by many users. Changing the default of explicit_defaults_for_timestamp is not a bug fix.
Task
Critical
For a beginner and even for experienced users coming from other databases this feature is very confusing.