|
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)
|
|