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

System versioning is incorrect in Spider

Details

    Description

      "information_schema.partitions" contains incorrect data about partitions for tables with engine=Spider (table_rows). Also request "select * from t_sp1 partition (p0)" returns data from current partition not from historical

      Test:

      --source include/have_innodb.inc
      --source include/have_partition.inc
       
      install soname 'ha_spider';
      set spider_same_server_link= on;
       
      eval create server s foreign data wrapper mysql options 
      (host '127.0.0.1', database 'test', user 'root', port $MASTER_MYPORT);
       
      CREATE TABLE t2(
         x INT,
         y INT,
         row_start TIMESTAMP(6),
         row_end TIMESTAMP(6)
      );
       
      create or replace table t_sp1 (x int,y int)
      engine=Spider COMMENT='wrapper "mysql", srv "s", table "t2"' 
      with system versioning partition by system_time (
          PARTITION p0 HISTORY,
          PARTITION pn CURRENT
      ); 
       
      insert into t_sp1 values (1,1),(2,2),(3,3),(4,4),(5,5);
       
      delete from t_sp1 where x=1;
       
      select * from t_sp1;
      select * from t_sp1 partition (p0);
       
      select partition_name, table_rows from information_schema.partitions where table_name = 't_sp1';
       
      drop tables t2, t_sp1;
      

      Actual result:

      select * from t_sp1;
      x	y
      2	2
      3	3
      4	4
      5	5
      select * from t_sp1 partition (p0);
      x	y
      2	2
      3	3
      4	4
      5	5
      select partition_name, table_rows from information_schema.partitions where table_name = 't_sp1';
      partition_name	table_rows
      p0	2
      pn	2
      

      Expected result:

      select * from t_sp1;
      x	y
      2	2
      3	3
      4	4
      5	5
      select * from t_sp1 partition (p0);
      x	y
      1	1
      select partition_name, table_rows from information_schema.partitions where table_name = 't_sp1';
      partition_name	table_rows
      p0	1
      pn	4
      

      Attachments

        1. 28413.result
          0.7 kB
        2. 28413.test
          0.7 kB

        Issue Links

          Activity

            ycp Yuchen Pei added a comment -

            Thanks for looking into this, midenok. I noticed that the issue used to "repeat for all acceptable engines", but the current description only mentions spider/partition. Is this mainly a runtime/versioned table issue, or a spider/partition issue? CC holyfoot.

            ycp Yuchen Pei added a comment - Thanks for looking into this, midenok . I noticed that the issue used to "repeat for all acceptable engines", but the current description only mentions spider/partition. Is this mainly a runtime/versioned table issue, or a spider/partition issue? CC holyfoot .

            Data is duplicated into all partititions nonetheless system versioning or not. See attached 28413.test. Please link the ticket number if it is the known issue.

            midenok Aleksey Midenkov added a comment - Data is duplicated into all partititions nonetheless system versioning or not. See attached 28413.test. Please link the ticket number if it is the known issue.
            ycp Yuchen Pei added a comment - - edited

            midenok: yes, it is expected.

            The usecase of spider tables with partitioning is that each partition of a spider table refers to tables on the data node, rather than individual partitions.

            For example:

            set spider_same_server_link= on;
            create server s foreign data wrapper mysql options
            (host '127.0.0.1', database 'test', user 'root', port 19000);
            CREATE TABLE t21(x INT, y INT);
            CREATE TABLE t22(x INT, y INT);
            CREATE TABLE t23(x INT, y INT);
            create or replace table t_sp2 (x int, y int)
            engine=Spider COMMENT='wrapper "mysql", srv "s"'
            partition by hash(x) partitions 3
            (partition p0 COMMENT='table "t21"',
            partition p1 COMMENT='table "t22"',
            partition p2 COMMENT='table "t23"');
            insert into t_sp2 values (1,1), (2,2), (3,3);
            select * from t_sp2 partition (p0);
            x	y
            3	3
            select * from t_sp2 partition (p1);
            x	y
            1	1
            select * from t_sp2 partition (p2);
            x	y
            2	2
            select * from t21;
            x	y
            3	3
            select * from t22;
            x	y
            1	1
            select * from t23;
            x	y
            2	2
            drop tables t21, t22, t23, t_sp2;
            

            And when the connection info of individual partitions of a spider table is not specified, like in your example, the table level connection info is used, i.e. each partition in the spider table will connect to t2. Therefore the INSERT statement inserts all values to t2, and each of the SELECT statement causes spider to select from the (same) remote table i.e. t2.

            Here I include your case for reference

            set spider_same_server_link= on;
            create server s foreign data wrapper mysql options
            (host '127.0.0.1', database 'test', user 'root', port 19000);
            CREATE TABLE t2(
            x INT,
            y INT,
            row_start TIMESTAMP(6),
            row_end TIMESTAMP(6)
            )
            partition by hash(x) partitions 3;
            create or replace table t_sp2 (x int, y int)
            engine=Spider COMMENT='wrapper "mysql", srv "s", table "t2"'
            partition by hash(x) partitions 3;
            insert into t_sp2 values (1,1), (2,2), (3,3);
            select * from t_sp2 partition (p0);
            x	y
            3	3
            1	1
            2	2
            select * from t_sp2 partition (p1);
            x	y
            3	3
            1	1
            2	2
            select * from t_sp2 partition (p2);
            x	y
            3	3
            1	1
            2	2
            drop tables t2, t_sp2;
            

            ycp Yuchen Pei added a comment - - edited midenok : yes, it is expected. The usecase of spider tables with partitioning is that each partition of a spider table refers to tables on the data node, rather than individual partitions. For example: set spider_same_server_link= on ; create server s foreign data wrapper mysql options (host '127.0.0.1' , database 'test' , user 'root' , port 19000); CREATE TABLE t21(x INT , y INT ); CREATE TABLE t22(x INT , y INT ); CREATE TABLE t23(x INT , y INT ); create or replace table t_sp2 (x int , y int ) engine=Spider COMMENT= 'wrapper "mysql", srv "s"' partition by hash(x) partitions 3 (partition p0 COMMENT= 'table "t21"' , partition p1 COMMENT= 'table "t22"' , partition p2 COMMENT= 'table "t23"' ); insert into t_sp2 values (1,1), (2,2), (3,3); select * from t_sp2 partition (p0); x y 3 3 select * from t_sp2 partition (p1); x y 1 1 select * from t_sp2 partition (p2); x y 2 2 select * from t21; x y 3 3 select * from t22; x y 1 1 select * from t23; x y 2 2 drop tables t21, t22, t23, t_sp2; And when the connection info of individual partitions of a spider table is not specified, like in your example, the table level connection info is used, i.e. each partition in the spider table will connect to t2. Therefore the INSERT statement inserts all values to t2, and each of the SELECT statement causes spider to select from the (same) remote table i.e. t2. Here I include your case for reference set spider_same_server_link= on ; create server s foreign data wrapper mysql options (host '127.0.0.1' , database 'test' , user 'root' , port 19000); CREATE TABLE t2( x INT , y INT , row_start TIMESTAMP (6), row_end TIMESTAMP (6) ) partition by hash(x) partitions 3; create or replace table t_sp2 (x int , y int ) engine=Spider COMMENT= 'wrapper "mysql", srv "s", table "t2"' partition by hash(x) partitions 3; insert into t_sp2 values (1,1), (2,2), (3,3); select * from t_sp2 partition (p0); x y 3 3 1 1 2 2 select * from t_sp2 partition (p1); x y 3 3 1 1 2 2 select * from t_sp2 partition (p2); x y 3 3 1 1 2 2 drop tables t2, t_sp2;

            Please review bb-10.5-midenok

            midenok Aleksey Midenkov added a comment - Please review bb-10.5-midenok
            ycp Yuchen Pei added a comment - - edited

            midenok and nikitamalyavin: I would like to review this patch too. Please don't push it until it's passed my review.

            I can see several commits in bb-10.5-midenok that is not in the current 10.5, and I assume the patch that needs to be reviewed for this issue is

            b4465fc6b51 * MDEV-28413 System versioning is incorrect in Spider
            

            So I will be reviewing this commit. midenok: please let me know if you want different commits reviewed.

            ycp Yuchen Pei added a comment - - edited midenok and nikitamalyavin : I would like to review this patch too. Please don't push it until it's passed my review. I can see several commits in bb-10.5-midenok that is not in the current 10.5, and I assume the patch that needs to be reviewed for this issue is b4465fc6b51 * MDEV-28413 System versioning is incorrect in Spider So I will be reviewing this commit. midenok : please let me know if you want different commits reviewed.
            ycp Yuchen Pei added a comment - - edited Added review comments in the mailing list, see https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/thread/S6Z2WBNSWU23X2BQCTXOAGM4IVE6N5LM/

            People

              midenok Aleksey Midenkov
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.