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.