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

Information schema shows wrong statistics about Spider partitions right after Spider table creation

    XMLWordPrintable

    Details

      Description

      Steps to reproduce:

      1. Launch two docker containers.

      2. Create two databases test1 and test2 on one of the servers.

      3. Create two empty tables, test1.user and test2.user with the following schema:

      CREATE TABLE user
      (
          id          INT UNSIGNED NOT NULL,
          username    VARCHAR(50),
          password    VARCHAR(50)
      )
      ENGINE = InnoDB;
      

      4. On the other server create table test.user with the following schema:

      CREATE TABLE user
      (
          id          INT UNSIGNED NOT NULL,
          username    VARCHAR(50),
          password    VARCHAR(50)
      )
      ENGINE = SPIDER
      PARTITION BY KEY(id) (
          PARTITION p0 COMMENT 'user "root", password "root", host "172.17.0.4", port "3306", database "test1", table "user"',
          PARTITION p1 COMMENT 'user "root", password "root", host "172.17.0.4", port "3306", database "test2", table "user"'
      );
      

      5. Review the statistics about newly created partitions:

       
      MariaDB [test]> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'user'\G
      *************************** 1. row ***************************
          table_name: user
      partition_name: p0
          table_rows: 2
      *************************** 2. row ***************************
          table_name: user
      partition_name: p1
          table_rows: 2
      2 rows in set (0.004 sec)
      

      According to the output each remote partition has 2 rows each. But the remote tables, that serve as partitions for a SPIDER table, a empty.

      Another way to reproduce:
      1. Create two docker containers. Install Spider plugin on one of them.

      2. Create two databases, test1 and test2, inside a container that doesn't have Spider.

      3. Create database test inside a container with Spider plugin.

      4. Create table test.user:

      CREATE TABLE user
      (
          id          INT UNSIGNED NOT NULL,
          username    VARCHAR(50),
          password    VARCHAR(50)
      )
      ENGINE = SPIDER
      PARTITION BY KEY(id) (
          PARTITION p0 COMMENT 'user "root", password "root", host "172.17.0.4", port "3306", database "test1", table "user"',
          PARTITION p1 COMMENT 'user "root", password "root", host "172.17.0.4", port "3306", database "test2", table "user"'
      );
      

      5. View the statistics:

      MariaDB [test]> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'user'\G
      *************************** 1. row ***************************
          table_name: user
      partition_name: p0
          table_rows: 0
      *************************** 2. row ***************************
          table_name: user
      partition_name: p1
          table_rows: 0
      2 rows in set, 1 warning (0.005 sec)
      

      6. Create table test1.user on another container:

      CREATE TABLE user
      (
          id          INT UNSIGNED NOT NULL,
          username    VARCHAR(50),
          password    VARCHAR(50)
      )
      ENGINE = InnoDB;
      

      And review the statstics on a container with Spider:

      MariaDB [test]> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'user'\G
      *************************** 1. row ***************************
          table_name: user
      partition_name: p0
          table_rows: 2
      *************************** 2. row ***************************
          table_name: user
      partition_name: p1
          table_rows: 0
      2 rows in set, 1 warning (0.003 sec)
      

      7. Repeat previous step by creating table test2.user

        Attachments

          Activity

            People

            Assignee:
            nayuta-yanagisawa Nayuta Yanagisawa
            Reporter:
            zse Sergey Zhylinsky
            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.