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

Information schema shows wrong statistics about InnoDB partitions after partitions coalesce

    XMLWordPrintable

    Details

      Description

      That was found for HASH partitioning type when creating tables for InnoDB storage engine. MyISAM and Aria have also been checked. Both these engines doesn't have this issue, only InnoDB has.

      Steps to reproduce:

      1. Create table partitioned by HASH with 8 partitions:

      MariaDB [test]> CREATE TABLE article (
          ->     id      INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
          ->     date    DATE NOT NULL,
          ->     author  VARCHAR(100) NOT NULL,
          ->     text    TEXT,
          ->     PRIMARY KEY(id, date)
          -> )
          -> ENGINE = InnoDB
          -> PARTITION BY HASH(YEAR(date))
          -> PARTITIONS 8;
      Query OK, 0 rows affected (0.070 sec)
      

      2. Insert some data:

      MariaDB [test]> INSERT INTO article(date, author, text)
          -> VALUES
          ->     ('1989-01-01', 'Roland Stadler', 'Some content'),
          ->     ('1995-01-01', 'Martin Fowler', 'Design Patterns'),
          ->     ('1998-01-01', 'Robert C. Martin', 'Clean Code');
      Query OK, 3 rows affected (0.003 sec)
      Records: 3  Duplicates: 0  Warnings: 0
      

      3. Review newly created partitions in information_schema:

      MariaDB [test]> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'article'\G
      *************************** 1. row ***************************
          table_name: article
      partition_name: p0
          table_rows: 0
      *************************** 2. row ***************************
          table_name: article
      partition_name: p1
          table_rows: 0
      *************************** 3. row ***************************
          table_name: article
      partition_name: p2
          table_rows: 0
      *************************** 4. row ***************************
          table_name: article
      partition_name: p3
          table_rows: 1
      *************************** 5. row ***************************
          table_name: article
      partition_name: p4
          table_rows: 0
      *************************** 6. row ***************************
          table_name: article
      partition_name: p5
          table_rows: 1
      *************************** 7. row ***************************
          table_name: article
      partition_name: p6
          table_rows: 1
      *************************** 8. row ***************************
          table_name: article
      partition_name: p7
          table_rows: 0
      8 rows in set (0.002 sec)
      

      4. Coalesce partitions to 4:

      MariaDB [test]> ALTER TABLE article COALESCE PARTITION 4;
      Query OK, 3 rows affected (0.086 sec)
      Records: 3  Duplicates: 0  Warnings: 0
      

      5. Review again partitions in information schema:

      MariaDB [test]> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'article'\G
      *************************** 1. row ***************************
          table_name: article
      partition_name: p0
          table_rows: 0
      *************************** 2. row ***************************
          table_name: article
      partition_name: p1
          table_rows: 0
      *************************** 3. row ***************************
          table_name: article
      partition_name: p2
          table_rows: 0
      *************************** 4. row ***************************
          table_name: article
      partition_name: p3
          table_rows: 0
      4 rows in set (0.002 sec)
      

      6. According to the output, partitions don't have any rows. But they do exist:

      MariaDB [test]> SELECT * FROM article;
      +----+------------+------------------+-------------------------------+
      | id | date       | author           | text                          |
      +----+------------+------------------+-------------------------------+
      |  1 | 1989-01-01 | Roland Stadler   | Some content |
      |  3 | 1998-01-01 | Robert C. Martin | Clean Code                    |
      |  2 | 1995-01-01 | Martin Fowler    | Design Patterns               |
      +----+------------+------------------+-------------------------------+
      3 rows in set (0.000 sec)
      

      7. Quering individual partitions also show that some of them do have rows:

      MariaDB [instagram]> select * from article partition (p0)\G
      Empty set (0.001 sec)
       
      MariaDB [instagram]> select * from article partition (p1)\G
      *************************** 1. row ***************************
          id: 1
        date: 1989-01-01
      author: Roland Stadler
        text: Some content
      1 row in set (0.001 sec)
       
      MariaDB [instagram]> select * from article partition (p2)\G
      *************************** 1. row ***************************
          id: 3
        date: 1998-01-01
      author: Robert C. Martin
        text: Clean Code
      1 row in set (0.001 sec)
       
      MariaDB [instagram]> select * from article partition (p3)\G
      *************************** 1. row ***************************
          id: 2
        date: 1995-01-01
      author: Martin Fowler
        text: Design Patterns
      1 row in set (0.001 sec)
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            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.