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

Enabling system versioning on tables without primary key breaks replication

    XMLWordPrintable

Details

    Description

      MariaDB [(mydata)]> show variables like "binlog_format";
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | binlog_format | ROW   |
      +---------------+-------+
      1 row in set (0.001 sec)
       
      MariaDB [mydata]> show variables like 'system%';
      +---------------------------------+---------+
      | Variable_name                   | Value   |
      +---------------------------------+---------+
      | system_time_zone                | UTC     |
      | system_versioning_alter_history | ERROR   |
      | system_versioning_asof          | DEFAULT |
      +---------------------------------+---------+
      3 rows in set (0.000 sec)
       
      MariaDB [mydata]> show variables like 'secure%';
      +------------------+-------+
      | Variable_name    | Value |
      +------------------+-------+
      | secure_auth      | ON    |
      | secure_file_priv | /tmp/ |
      | secure_timestamp | NO    |
      +------------------+-------+
      3 rows in set (0.000 sec)
       
      MariaDB [mydata]> show variables like 'autocommit';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | autocommit    | ON    |
      +---------------+-------+
      1 row in set (0.001 sec)
       
      MariaDB [mydata]> create table mytest as select * from mysql.innodb_index_stats where 0=9;
      Query OK, 0 rows affected (0.015 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [mydata]> alter table mytest add system versioning;
      Query OK, 0 rows affected (0.020 sec)              
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [mydata]> show create table mytest\G
      *************************** 1. row ***************************
             Table: mytest
      Create Table: CREATE TABLE `mytest` (
        `database_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
        `table_name` varchar(199) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
        `index_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
        `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        `stat_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
        `stat_value` bigint(20) unsigned NOT NULL,
        `sample_size` bigint(20) unsigned DEFAULT NULL,
        `stat_description` varchar(1024) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING
      1 row in set (0.000 sec) 
       
      MariaDB [mydata]> insert into mytest select * from mysql.innodb_index_stats;
      Query OK, 80 rows affected (0.005 sec)
      Records: 80  Duplicates: 0  Warnings: 0
       
      MariaDB [mydata]> insert into mytest select * from mysql.innodb_index_stats;
      Query OK, 80 rows affected (0.005 sec)
      Records: 80  Duplicates: 0  Warnings: 0
       
      MariaDB [mydata]> insert into mytest select * from mysql.innodb_index_stats;
      Query OK, 80 rows affected (0.006 sec)
      Records: 80  Duplicates: 0  Warnings: 0
       
      MariaDB [mydata]> select count(*) from mytest;
      +----------+
      | count(*) |
      +----------+
      |      240 |
      +----------+
      1 row in set (0.001 sec)
       
      MariaDB [mydata]> select count(*) from mytest where table_name='customers';
      +----------+
      | count(*) |
      +----------+
      |       21 |
      +----------+
      1 row in set (0.000 sec)
       
      MariaDB [mydata]> delete from mytest where table_name='customers';
      Query OK, 21 rows affected (0.004 sec)
       
      MariaDB [mydata]> select count(*) from mytest where table_name='customers';
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.000 sec)
       
       
      ON REPLICA
      ====================
      MariaDB [mydata]> select count(*) from mytest where table_name='customers';
      +----------+
      | count(*) |
      +----------+
      |       14 |
      +----------+
      1 row in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              bnestere Brandon Nesterenko
              mpflaum Maria M Pflaum
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.