Set Explicit Default for all Timestamp Values in MySQL Schema

[shawnhathaway]

During the clusterMembership table schema creation, I found MySQL has some hidden default value behavior for Timestamp columns that are declared NOT NULL in some cases. This caused the schema to work correctly on my local mysql (version: 8.0.19 Homebrew) but failed on the server mysql (version: 5.7).

From https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp:

If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMP columns as follows:

TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value of NULL is permitted and sets the column to the current timestamp.

The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

TIMESTAMP columns following the first one, if not explicitly declared with the NULL attribute or an explicit DEFAULT attribute, are automatically declared as DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is enabled, a default value of '0000-00-00 00:00:00' may be invalid. Be aware that the TRADITIONAL SQL mode includes strict mode and NO_ZERO_DATE. See Section 5.1.10, “Server SQL Modes”.

[samarabbas]

Moving to stabilization. This is backwards compatible change.

[samarabbas]

@yiminc @wxing1292 can you confirm if this is still relevant?

[wxing1292]

we will re-evaluate this issue when supporting mysql 8.0

[yiminc]

now server support mysql 8.0.19. https://github.com/temporalio/temporal/pull/2210

[qiuyuzhou]

In temporal v1.7: TIMESTAMP DEFAULT '1970-01-01 00:00:01'

This don't work when the time zone is '+8:00', because it would be subtracted 8 hours when convert it to UTC time zone, then would be an invalid value.

In temporal master branch: TIMESTAMP DEFAULT '1970-01-01 00:00:01+00:00'

The timezone offset is only supported from mysql v8.0.19. This format has failed on my mysql v8.0.13 server.

[robholland]

Lets set it to '1970-01-02 00:00:01' so that it's safe against timezone adjustment but doesn't require any particular MySQL release.

[robholland]

This is preventing install on the current MariaDB version on AWS RDS (10.6.8).