Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29078

For old binary logs explicit_defaults_for_timestamp presumed to be OFF, server value ignored

    XMLWordPrintable

    Details

      Description

      Before MDEV-28632, the server uses its own value of explicit_defaults_for_timestamp while replaying/replicating binary logs.

      Now it expects to find explicit_defaults_for_timestamp in the binary log, which is good; but when it's not there (the log is created by a server which doesn't yet have this functionality), it apparently falls back to "always OFF".

      While it will work for the vast majority of old masters (those which have the default explicit_defaults_for_timestamp=off), it will make OM => NS replication impossible for those remaining ones which have non-default explicit_defaults_for_timestamp=on already.

      The other option would be to fall back to the value configured on the slave instead. It will require an additional action from users with the default OFF on the old master, but it will keep the replication configurable for those who have ON on the master.

      So, it is a judgement call which needs to be thought through and at least documented explicitly.

      MySQL seems to use the server value in such case.

      An example of the old log is attached for convenience (naturally it is easy to create by any existing server). It was produced on a 10.6.8 server started with explicit_defaults_for_timestamp=on and binlog_format=row. A table with two timestamps was created

      #220710 21:26:03 server id 101  end_log_pos 534 CRC32 0x742afba6 	GTID 0-101-2 ddl
      /*!100001 SET @@session.gtid_seq_no=2*//*!*/;
      # at 534
      #220710 21:26:03 server id 101  end_log_pos 653 CRC32 0x766a78ae 	Query	thread_id=6	exec_time=0	error_code=0	xid=6
      use `test`/*!*/;
      SET TIMESTAMP=1657477563/*!*/;
      create table t (t1 timestamp, t2 timestamp)
      /*!*/;
      # at 653
      

      and a (NULL,NULL) row was inserted.

      #220710 21:26:10 server id 101  end_log_pos 750 CRC32 0xba62832c 	Annotate_rows:
      #Q> insert into t values (null,null)
      #220710 21:26:10 server id 101  end_log_pos 797 CRC32 0x5258822f 	Table_map: `test`.`t` mapped to number 18
      # at 797
      #220710 21:26:10 server id 101  end_log_pos 831 CRC32 0x957e2b6f 	Write_rows: table id 18 flags: STMT_END_F
      ### INSERT INTO `test`.`t`
      ### SET
      ###   @1=NULL
      ###   @2=NULL
      # Number of rows: 1
      

      On the original server the result is a table with nullable columns and NULL values (as the row event also demonstrates),

      10.6.8

      MariaDB [test]> show create table t \G
      *************************** 1. row ***************************
             Table: t
      Create Table: CREATE TABLE `t` (
        `t1` timestamp NULL DEFAULT NULL,
        `t2` timestamp NULL DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from t;
      +------+------+
      | t1   | t2   |
      +------+------+
      | NULL | NULL |
      +------+------+
      1 row in set (0.001 sec)
      

      while on the preview server started with the new default explicit_defaults_for_timestamp=on, after replaying the log it is

      bb-10.10-MDEV-28632 c4407143a2

      MariaDB [test]> show create table t \G
      *************************** 1. row ***************************
             Table: t
      Create Table: CREATE TABLE `t` (
        `t1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        `t2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from t;
      +---------------------+---------------------+
      | t1                  | t2                  |
      +---------------------+---------------------+
      | 2022-07-10 21:26:10 | 0000-00-00 00:00:00 |
      +---------------------+---------------------+
      1 row in set (0.001 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.