WL#9687: Change default for explicit_defaults_for_timestamp to ON

In MySQL 5.6 an option for explicit_defaults_for_timestamp was introduced (and
deprecated) with the intention of suggesting users to change their habits -
WL#6292.
In most cases though, users just ignore the warnings from
explicit_defaults_for_timestamp.
It makes more sense to change the default to TRUE, with users able to revert back
to the FALSE behavior for one major version. This means removal in 9.0.
Related future work:
* Remove explicit-defaults-for-timestamp: command line option and global
variable
* Remove explicit_defaults_for_timestamp: session variable
User Documentation
==================
* https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-2.html
*
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
* https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

* Functional requirements
FR1. Default value of variable 'explicit_defaults_for_timestamp' at server
start should be : 1
FR2. Replication shall not break - neither same-version or
cross-version, and not in chained topologies. In other words,
when user does provide a value for
explicit_defaults_for_timestamp, table definitions that depend on
explicit_defaults_for_timestamp shall be the same on all nodes in
the topology.
There is a framework already which preserves
explicit_defaults_for_timestamp throughout the replication
topology. Therefore, this is expected to work without any code
change. The following should be tested to cover all cases:
- 8.0 -> 8.0
- 5.7 -> 8.0 -> 8.0
- 8.0 -> 5.7 -> 5.7 [not officially supported, but works unless
there is a bug in the existing framework]

Introduction
------------
explicit_defaults_for_timestamp is a global dynamic variables introduced in
scope of WL#6292 to make the behavior of timestamp standard. The default value
of the variable is FALSE. This worklog will change the default value of
explicit_defaults_for_timestamp to TRUE. Test cases will be changed to execute
with the new default behavior.
Abbreviation : EDFT : explict_defaults_for_timestamp
2. Statements affected by explicit_defaults_for_timestamp
---------------------------------------------------------
Though the above pages mentions the differences in behavior with different
values of explicit_defaults_for_timestamp, we will highlight the difference in
behavior with respect to statements and discuss the effect of change in default
behavior with respect to version replication.
2.1. DDL - CREATE statements :
-----------------------------
i> One timestamp column with default sql_mode:
CREATE TABLE t21 (a timestamp);
INSERT INTO t21 VALUES();
INSERT INTO t21 VALUES(NULL);
Case : explicit_defaults_for_timestamp= FALSE
NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP is
assigned to the column by default. Above statements passes, inserts
current_timestamp.
Case : explicit_defaults_for_timestamp= TRUE
column gets 'default NULL'. NULL is inserted into the table.
ii> Two timestamp columns with default sql_mode:
CREATE TABLE t22 (a TIMESTAMP , b TIMESTAMP);
Case : explicit_defaults_for_timestamp= FALSE
Statements fails with error - Invalid default value for 'b'
Statement passes if NO_ZERO_DATE sql_mode is relaxed.
Case : explicit_defaults_for_timestamp= TRUE
Statement passes, creates table with 'default NULL' for both
timestamp columns.
iii> Explicit 'default NULL' (behavior is independent of sql_mode).
CREATE TABLE t50(a TIMESTAMP DEFAULT NULL);
Case : explicit_defaults_for_timestamp= FALSE
Statements fails with error - Invalid default value for 'a'
Case : explicit_defaults_for_timestamp= TRUE
Statement passes, creates table with 'default NULL' for timestamp
column.
2.2.DDL - ALTER statement :
--------------------------
ALTER will follow the same patters as CREATE statements.
For example with default sql_mode(STRICT + NO_ZERO_DATE):
CREATE TABLE t51(a TIMESTAMP);
ALTER TABLE t51 ADD COLUMN b TIMESTAMP;
Case : explicit_defaults_for_timestamp= FALSE
ALTER fails with error : Invalid default value for 'b'
Case : explicit_defaults_for_timestamp= TRUE
ALTER passes.
2.3.DML - INSERT / UPDATEs :
---------------------------
While table is created only once, DML can keep on happening.
It is possible to create same table structure irrespective of the value of
explicit_defaults_for_timestamp.
Even in this case, INSERT will depend on the value of
explicit_defaults_for_timestamp.
Consider the case :
i> SQL mode= default
CREATE TABLE `t100` (`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP);
INSERT INTO t100 VALUES(NULL);
Case : explicit_defaults_for_timestamp= FALSE
INSERT passes, enters current timestamp to the table.
Case : explicit_defaults_for_timestamp= TRUE
INSERT fails with error : Column 'a' cannot be null
The case is similar with other DML statements ( UPDATE t100 SET a=NULL; )
ii> sql_mode=''
SET sql_mode='';
CREATE TABLE `t100` (`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP);
INSERT INTO t100 VALUES(NULL), (NULL);
Case : explicit_defaults_for_timestamp= FALSE
Inserts current timestamp values for both rows.
Case : explicit_defaults_for_timestamp= TRUE
Inserts '0000-00-00 00:00:00' for both rows.
3 Effect on Replication :
-------------------------
3.1. Replication between two new servers or cross version replication should
work provided that both have the same value of
--explicit-defaults-for-timestamp start up parameter.
3.2. Effect on replication with two servers having different value of
explicit-defaults-for-timestamp :
There are 2 cases to consider here :
i> Master : EDFT = 0 Slave : EDFT= 1
ii> Master : EDFT = 1 Slave : EDFT= 0
Replication should not break in both the cases.
4. Deprecation Warning :
------------------------
Whenever the value of explicit_defaults_for_timestamp is changed from TRUE to
FALSE, user should get the deprecation warning :
'explicit_defaults_for_timestamp' is deprecated and will be removed in a future
release.
5. Fix of test cases:
----------------------
The test cases have been fixed to work with new default behavior.
Any test specific explanation will be explicitly mentioned.
The fix mostly uses one of the following reasons.
i> Record new default value
ii> Record non promotion of timestamp columns.
iii> Timestamp columns are now nullable by default and no auto promotion
happens. Record test output.
iv> Test tried to insert ZERO date in NO_ZERO_DATE mode. This is not allowed
for timestamp column by default.
v> Fix sql mode to test the intended behavior of the test and record result
file.
vi> Error scenario to check table creation with 2 timestamp column fails with
NO_ZERO_DATE. Deprecated behavior. Remove the test scenario.
vii> Extra warning is there when trying to insert NULL to TIMESTAMP NOT NULL
column. Value inserted in the table changed from current_timestamp value to
'0000-00-00 00:00:00'. Recorded the result file.