[MDEV-28470] Please remove automatic TIMESTAMP update behaviour Created: 2022-05-04  Updated: 2022-05-20  Resolved: 2022-05-20

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update, Temporal Types
Fix Version/s: N/A

Type: Task Priority: Critical
Reporter: Lukas Eder Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 6
Labels: None

Issue Links:
Duplicate
duplicates MDEV-28632 Change default of explicit_defaults_f... Closed

 Description   

I was going to report this bug:

CREATE TABLE t (
 
  id INTEGER NOT NULL,
 
  t1 TIMESTAMP NOT NULL,
 
  t2 TIMESTAMP,
 
  CONSTRAINT pk_t PRIMARY KEY (id)
 
);
 
 
 
INSERT INTO t (id, t1) 
VALUES (1, timestamp '1970-01-01 01:00:38.0');
 
 
 
UPDATE t 
SET
 
  t.t2 = timestamp '1970-01-01 01:02:10.0';
 
  
SELECT * FROM t;

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

 Comments   
Comment by Simon Martinelli [ 2022-05-04 ]

For a beginner and even for experienced users coming from other databases this feature is very confusing.

Comment by Mr Niall Litchfield [ 2022-05-04 ]

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

Comment by Sergei Golubchik [ 2022-05-05 ]

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.

Comment by Lukas Eder [ 2022-05-06 ]

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.

Comment by Alejandro Duarte [ 2022-05-06 ]

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).

Comment by Ralf Gebhardt [ 2022-05-20 ]

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.

Comment by Ralf Gebhardt [ 2022-05-20 ]

MDEV-28632 has been created to describe the needed change

Comment by Lukas Eder [ 2022-05-20 ]

Thanks a lot for addressing this!

Generated at Thu Feb 08 10:01:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.