MySQL WL#6292 - Make TIMESTAMP columns nullable by default

revno: 3690.68.37

committer: Gopal Shankar <gopal.shankar@oracle.com>

branch nick: mysql-trunk-wl6292-push

timestamp: Thu 2012-06-21 08:27:40 +0530

message:

  WL#6292 - Make TIMESTAMP columns nullable by default.

  This worklog implements new behavior for TIMESTAMP columns,

  defined by CREATE TABLE, ALTER TABLE and CREATE SELECT commands.

  This is in effort to make TIMESTAMP columns behavior to be

  more closer to SQL standard. The change in behavior is as

  described below,

  Current behavior:

  =================

    1) Unlike all the other types, TIMESTAMP columns which are not

       explicitly specified as NULLable automatically get NOT NULL

       as attribute.

    2) If the first TIMESTAMP column in table is not specified as

       NULLable and doesn't have explicit DEFAULT or ON UPDATE

       value specified it automatically gets DEFAULT NOW()

       ON UPDATE NOW() as attributes.

    3) All other TIMESTAMP columns which are not NULLable and

       don't have explicit default specified get '0' as default

       value and treated as having explicit default value after

       that (i.e. if you don't provide explicit value for such

       a column when inserting into table no warning or error

       is emitted).

  New behavior:

  =============

    1) TIMESTAMP columns which are not explicitly specified as

       NOT NULL become NULLable.

    2) No TIMESTAMP columns get DEFAULT NOW() or ON UPDATE NOW()

       attributes automatically, without them being explicitly

       specified.

    3) Non-NULLable TIMESTAMP columns without explicit default

       value treated as having no default value. I.e. warning

       or error is emitted (depends on sql_mode) if we insert

       a row without providing and explicit value for such a

       column. In case when warning is emitted and not an

       error such a column will still get '0' if no explicit

       value was specified for it.

  A new command line option '--explicit_defaults_for_timestamp'

  is introduced for MySQL server to enable new behavior. The old

  behavior is kept as default, so that the existing applications

  assuming old semantics work without any changes.

  Replication slave applier thread is extended with new interface,

  such that the slave applies the logs with old behavior, if the

  logs are generated by master with older version than slave.

  MySQL system table definitions at scripts/* were updated,

  such that they work fine with old and new behavior with

  any change. MTR tests which use TIMESTAMP columns are also

  updated, such that the test works both in old and new

  behavior. This should help easy transition to new behavior

  going forward.

  This worklog addresses Bug#11762529 and Bug#13344629.