Elena Stepanova added a comment - 2022-06-14 14:08 A few examples to remind of the oddities caused by the current default behavior (with explicit_defaults_for_timestamp=OFF). Only the first TIMESTAMP column gets the CURRENT_TIMESTAMP, but all TIMESTAMP columns get implicit NOT NULL and some default: MariaDB [test]> create table t (a timestamp , b int , c timestamp ); Query OK, 0 rows affected (0.037 sec)   MariaDB [test]> show create table t \G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `a` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `b` int (11) DEFAULT NULL , `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.001 sec) If you add a new column before the first TIMESTAMP, you get two CURRENT_TIMESTAMP columns: MariaDB [test]> alter table t add d timestamp first ; Query OK, 0 rows affected (0.037 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> show create table t \G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `d` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `a` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `b` int (11) DEFAULT NULL , `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.001 sec) But if you add a new column after an existing TIMESTAMP, you won't: MariaDB [test]> alter table t add e timestamp after a; Query OK, 0 rows affected (0.041 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> show create table t \G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `d` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `a` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `e` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' , `b` int (11) DEFAULT NULL , `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.001 sec) If you "modify" the first timestamp, it will remain as it is: MariaDB [test]> alter table t modify d timestamp ; Query OK, 0 rows affected (0.033 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> show create table t \G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `d` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `a` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `e` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' , `b` int (11) DEFAULT NULL , `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.001 sec) If you modify another timestamp, it will lose CURRENT_TIMESTAMP properties: MariaDB [test]> alter table t modify a timestamp ; Query OK, 0 rows affected (0.033 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> show create table t \G *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `d` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' , `e` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' , `b` int (11) DEFAULT NULL , `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.001 sec)

Sergei Golubchik added a comment - 2022-06-18 19:02 It's in this branch: preview-10.10-misc .

Elena Stepanova added a comment - 2022-06-29 22:02 - edited serg , ralf.gebhardt , In the current implementation, I only see the change of the default, but the scope and read-only-ness remained as they were, and there seems to be no activity on MDEV-8455 . I don't think it is sufficient. It is clear from the MySQL history that users don't accept the change in this form, because it breaks OM => NS upgrade. There had been multiple complaints about it ( example1 , example2 ) which made MySQL start writing it in the binary log , and for this, it was changed to dynamic and session-level. And it was done not only in 8.0 where the default was changed, but in 5.7, to facilitate the rolling upgrade. I think we have to do the same, in as early versions as possible and as soon as possible. While it won't eliminate the OM => NS problem entirely (there will still be ancient pre-change installations and binary logs), it will at least minimize the issue.

Elena Stepanova added a comment - 2022-08-02 18:13 - edited The last branch in my testing was bb-10.10- MDEV-28632 of a48739e6ee , with a patch similar to 387c9d1707 applied locally. Testing didn't reveal any relevant issues. I think the change should be pushed into the target branches. It is not what will be merged into 10.10 main though; the most part of the patch will go to 10.5+ and will be merged up, and only s smaller change will be pushed directly into 10.10. The change which old versions need to facilitate smooth(er) rolling upgrades turned out to be much more intrusive than I originally expected; it is worrisome, but I still believe it is the right thing to do: odd legacy behavior of timestamps has to be eventually abandoned, and given our release model, there is no way to do it other than accompanying it by a change in older post-GA versions. Documentation-wise, I don't think that the switch from read-only to dynamic variable should be anyhow advertised, neither for old versions nor for 10.10; while it is done on technical reasons, we do not actually want users to start changing it at runtime. It is never needed, whatever users want to achieve by changing the variable at runtime, they can do instead by creating a table in a proper manner. What should probably be highlighted for old GAs is that the default value of the variable will change in upcoming versions, so users are advised to start switching their instances to ON value as soon as possible globally , by setting it in the config file. For 10.10, the focus should be on the change of the default, users need to be aware if they rely on the old behavior in their applications. Galera users should pay special attention to it, since unlike for the traditional replication, nothing can be done to smoothen Galera rolling upgrades, users will have to handle the difference themselves.

Michael Widenius added a comment - 2022-11-29 10:36 Just a few comments. I think that the variable "explicit_defaults_for_timestamp" has a bad name. I cannot by just looking at: explicit_defaults_for_timestamp=OFF understand what it will do. First, the variable does affect CREATE TABLE, not defaults for existing timestamps. Why does setting the variable to ON mean that there are are no defaults? A better name would be something like timestamp_in_create_table_has_current_time_as_default. Note also that if we change timestamp to be NULL, it means it will take more storage space and if the user puts an index on the timestamp column, the performance for that index will be about 3-4 times slower (as the index will not anymore be fixed length)