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

System versioning for system tables does not work as expected

Details

    Description

      I would think that adding system versioning for (some) system tables is a reasonable idea, given that they are not updated so often, but are extremely important. However, it doesn't work (in different ways), a couple examples are below; there are probably more, I didn't try other tables yet. Not sure whether it should be fixed or prohibited, but either one or another should be done.

      alter table mysql.user add system versioning;
      		 
      # Query OK, 2 rows affected (0.04 sec)
      		 
       
      		 
      select user, host, sys_trx_start, sys_trx_end from mysql.user for system_time all;
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
      # | user | host      | sys_trx_start              | sys_trx_end                |
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
      # | root | localhost | 2017-12-28 19:40:13.453770 | 2038-01-19 05:14:07.999999 |
      		 
      # | root | 127.0.0.1 | 2017-12-28 19:40:13.453770 | 2038-01-19 05:14:07.999999 |
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
       
      		 
      create user foo@localhost;
      		 
      # Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      select user, host, sys_trx_start, sys_trx_end from mysql.user for system_time as of current_timestamp;
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
      # | user | host      | sys_trx_start              | sys_trx_end                |
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
      # | root | localhost | 2017-12-28 19:40:13.453770 | 2038-01-19 05:14:07.999999 |
      		 
      # | root | 127.0.0.1 | 2017-12-28 19:40:13.453770 | 2038-01-19 05:14:07.999999 |
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
       
      		 
      select user, host, sys_trx_start, sys_trx_end from mysql.user for system_time all;
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
      # | user | host      | sys_trx_start              | sys_trx_end                |
      		 
      # +------+-----------+----------------------------+----------------------------+
      		 
      # | root | localhost | 2017-12-28 19:40:13.453770 | 2038-01-19 05:14:07.999999 |
      		 
      # | root | 127.0.0.1 | 2017-12-28 19:40:13.453770 | 2038-01-19 05:14:07.999999 |
      		 
      # | foo  | localhost | NULL                       | 0000-00-00 00:00:00.000000 |
      		 
      # +------+-----------+----------------------------+----------------------------+


      alter table mysql.proc add system versioning;
      		 
      # Query OK, 2 rows affected (0.04 sec)
      		 
       
      		 
      create procedure pr() begin end;
      		 
      # ERROR 1805 (HY000): Column count of mysql.proc is wrong. Expected 21, found 23. The table is probably corrupted

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14942 System tables cannot be versioned (confirm limitation and document)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            would be good to make it work, of course.
            that'd mean

            • update versioning columns whenever the table is updated
            • ignore invisible_system columns in consistency checks
            serg Sergei Golubchik added a comment - would be good to make it work, of course. that'd mean update versioning columns whenever the table is updated ignore invisible_system columns in consistency checks
            midenok Aleksey Midenkov added a comment -

            Feature task is here: Versioned system tables (in mysql db) #474

            Meanwhile this task will be resolved as Prohibit.

            midenok Aleksey Midenkov added a comment - Feature task is here: Versioned system tables (in mysql db) #474 Meanwhile this task will be resolved as Prohibit.
            midenok Aleksey Midenkov added a comment -

            Using TABLE_CATEGORY is not enough as many system tables by fact are TABLE_CATEGORY_USER (see is_system_table_name()), thus all tables in mysql schema are prohibited for System Versioning.

            midenok Aleksey Midenkov added a comment - Using TABLE_CATEGORY is not enough as many system tables by fact are TABLE_CATEGORY_USER (see is_system_table_name() ), thus all tables in mysql schema are prohibited for System Versioning.

            People

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