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

System tables cannot be versioned (confirm limitation and document)

    XMLWordPrintable

    Details

      Description

      Intuitively, adding versioning to some system tables seems to be a good idea – they are not changed too often, information in them is important to keep save and to audit.

      However, it expectedly doesn't work in different ways.

      events

      MariaDB [mysql]> alter table mysql.event add system versioning;
      Query OK, 0 rows affected (0.04 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [mysql]> create event ev on schedule every 1 hour do set @a=1;
      ERROR 1545 (HY000): Failed to open mysql.event
      MariaDB [mysql]> flush tables;
      Query OK, 0 rows affected (0.02 sec)
       
      # In error log:
      2018-01-14  2:30:01 9 [ERROR] Column count of mysql.event is wrong. Expected 22, found 24. The table is probably corrupted
      

      user

      MariaDB [mysql]> alter table mysql.user add system versioning;
      Query OK, 6 rows affected (0.04 sec)
      Records: 6  Duplicates: 0  Warnings: 0
       
      MariaDB [mysql]> create user foo@localhost;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [mysql]> select user, host from mysql.user where user = 'foo';
      Empty set (0.00 sec)
       
      MariaDB [mysql]> select user, host, row_start, row_end from mysql.user for system_time all where user = 'foo';
      +------+-----------+-----------+----------------------------+
      | user | host      | row_start | row_end                    |
      +------+-----------+-----------+----------------------------+
      | foo  | localhost | NULL      | 0000-00-00 00:00:00.000000 |
      +------+-----------+-----------+----------------------------+
      1 row in set (0.01 sec)
      

      I didn't try other tables.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration