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

Attempt to insert into system versioning columns on old server may make slave data diverge

    XMLWordPrintable

    Details

      Description

      This probably just needs to be documented.

      In OM => NS replication with NS having MDEV-16546 and OM not having it, if the slave is running with global system_versioning_insert_history=on, INSERT IGNORE executed on the master in statement/mixed mode and inserting values into custom-made period columns will be accepted with the warnings, and values for row columns will be ignored; but it will be replicated to the slave "as is", and since the binlog of the old master doesn't enforce system_versioning_insert_history=off, it will be executed with the global value on and thus will be treated as inserting the history. So, the master and slave will end with different values.

      That is, on master

      set binlog_format=mixed;
      create or replace table t (a int, s timestamp(6) as row start, e timestamp(6) as row end, period for system_time(s,e)) with system versioning;
       insert ignore into t values (1,'2024-01-01','2025-01-01');
      

      will produce on the master

      10.3 78030b67b9

      MariaDB [test]>  insert ignore into t values (1,'2024-01-01','2025-01-01');
      Query OK, 1 row affected, 2 warnings (0.018 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+----------------------------------------------------------------------------+
      | Level   | Code | Message                                                                    |
      +---------+------+----------------------------------------------------------------------------+
      | Warning | 1906 | The value specified for generated column 's' in table 't' has been ignored |
      | Warning | 1906 | The value specified for generated column 'e' in table 't' has been ignored |
      +---------+------+----------------------------------------------------------------------------+
      

      | tomahawk-d11-bin.000002 | 691 | Query             |        11 |         819 | use `test`; insert ignore into t values (1,'2024-01-01','2025-01-01')                                              
      

      MariaDB [test]> select * from t for system_time all;
      +------+----------------------------+----------------------------+
      | a    | s                          | e                          |
      +------+----------------------------+----------------------------+
      |    1 | 2022-10-16 22:56:55.551973 | 2038-01-19 05:14:07.999999 |
      +------+----------------------------+----------------------------+
      1 row in set (0.001 sec)
      

      but on the slave it will be

      bb-10.11-MDEV-16546 32090722c72

      MariaDB [test]> select * from t for system_time all;
      +------+----------------------------+----------------------------+
      | a    | s                          | e                          |
      +------+----------------------------+----------------------------+
      |    1 | 2024-01-01 00:00:00.000000 | 2025-01-01 00:00:00.000000 |
      +------+----------------------------+----------------------------+
      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:
              2 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.