[MDEV-27397] Information schema shows wrong statistics about Spider partitions right after Spider table creation Created: 2021-12-31  Updated: 2023-05-23

Status: Confirmed
Project: MariaDB Server
Component/s: Information Schema, Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Sergey Zhylinsky Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates

 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

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2022-01-12 ]

Roel Could you verify the bug?

Comment by Roel Van de Paar [ 2022-01-15 ]

Bug confirmed as described. Docker is not needed. Change port as required.

INSTALL PLUGIN spider SONAME 'ha_spider.so';
 
SET SESSION spider_same_server_link=ON;
 
CREATE TABLE t_base(c1 INT) ENGINE = InnoDB;
 
CREATE TABLE t(c1 INT) ENGINE = SPIDER PARTITION BY KEY(c1) (PARTITION p0 COMMENT "HOST '127.0.0.1', PORT '29125', USER 'root', password '', DATABASE 'test', TABLE 't_base'", PARTITION p1 COMMENT "HOST '127.0.0.1', PORT '29125', USER 'root', password '', DATABASE 'test', TABLE 't_base'");
 
SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't';

Leads to:

10.8.0 347f6d01e3b570dce49aa1ab42cb83021905a14d (Debug)

 
10.8.0-dbg>CREATE TABLE t_base(c1 INT) ENGINE = InnoDB;
 
Query OK, 0 rows affected (0.013 sec)
 
 
 
10.8.0-dbg>CREATE TABLE t(c1 INT) ENGINE = SPIDER PARTITION BY KEY(c1) (PARTITION p0 COMMENT "HOST '127.0.0.1', PORT '29125', USER 'root', password '', DATABASE 'test', TABLE 't_base'", PARTITION p1 COMMENT "HOST '127.0.0.1', PORT '29125', USER 'root', password '', DATABASE 'test', TABLE 't_base'");
 
Query OK, 0 rows affected (0.343 sec)
 
 
 
10.8.0-dbg>SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't';
 
+------------+----------------+------------+
 
| table_name | partition_name | table_rows |
 
+------------+----------------+------------+
 
| t          | p0             |          2 |
 
| t          | p1             |          2 |
 
+------------+----------------+------------+
 
2 rows in set (0.014 sec)

Bug confirmed on 10.4-10.8, and likely present in earlier versions also.

Generated at Thu Feb 08 09:52:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.