Skip to content
  • Gopal Shankar's avatar
    daa4dddc
    WL#6292 - Make TIMESTAMP columns nullable by default. · daa4dddc
    Gopal Shankar authored
    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.
    daa4dddc
    WL#6292 - Make TIMESTAMP columns nullable by default.
    Gopal Shankar authored
    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.
Loading