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

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

          is caused by

          Task - A task that needs to be done. MDEV-16546 System versioning setting to allow history modification

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            serg Sergei Golubchik added a comment - - edited

            I can fix it. Normally, when in a version X.Y.Z we add a new setting, that's written to binlog, then when replicating NM=>NS, the slave will get its value from the master, and on OM=>NS, it'll use the actual configured value on the slave side.

            In this case, I can make it so that in OM=>NS the slave will take the value from the master, which will be 0, as OM doesn't have system_versioning_insert_history.

            serg Sergei Golubchik added a comment - - edited I can fix it. Normally, when in a version X.Y.Z we add a new setting, that's written to binlog, then when replicating NM=>NS, the slave will get its value from the master, and on OM=>NS, it'll use the actual configured value on the slave side. In this case, I can make it so that in OM=>NS the slave will take the value from the master, which will be 0, as OM doesn't have system_versioning_insert_history .

            People

              serg Sergei Golubchik
              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.