[MDEV-14790] System versioning for system tables does not work as expected Created: 2017-12-28  Updated: 2018-05-29  Resolved: 2018-04-10

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Versioned Tables
Affects Version/s: 10.3.4
Fix Version/s: 10.3.6

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-14942 System tables cannot be versioned (co... Closed

 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



 Comments   
Comment by Sergei Golubchik [ 2018-02-14 ]

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
Comment by Aleksey Midenkov [ 2018-02-27 ]

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

Meanwhile this task will be resolved as Prohibit.

Comment by Aleksey Midenkov [ 2018-02-28 ]

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.

Generated at Thu Feb 08 08:16:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.