Zvi
Zvi

Reputation: 2424

MySQL reorganize lose the partitions info

When I reorganize partitions of a table, some of the partitions info is lost.

Here is a test code, with explanations.

DROP DATABASE IF EXISTS bug;
CREATE DATABASE bug;
USE bug; 

CREATE TABLE test (id INT, purchased DATE)
PARTITION BY RANGE (YEAR(purchased)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

INSERT INTO test(id, purchased)
VALUES
    (1, '1990-01-01'),
    (2, '2001-01-01'),
    (3, '2011-01-01');

SELECT *
FROM test;
/*+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1990-01-01 |
|    2 | 2001-01-01 |
|    3 | 2011-01-01 |
+------+------------+*/

SELECT
    PARTITION_NAME, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE 1=1
    AND TABLE_NAME = 'test'
; -- one row per partition
/*+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          1 |
| p2             |          1 |
+----------------+------------+*/

/* now we want to split partition p0 to values up to year 1990 and up to 2000 */

ALTER TABLE test
REORGANIZE PARTITION p0 INTO (
    PARTITION n0 VALUES LESS THAN (1990),
    PARTITION n1 VALUES LESS THAN (2000)
);

SELECT *
FROM test;
/*+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1990-01-01 |
|    2 | 2001-01-01 |
|    3 | 2011-01-01 |
+------+------------+
The data is still in the table */

SELECT
    PARTITION_NAME, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE 1=1
    AND TABLE_NAME = 'test'
;
/*+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| n0             |          0 |
| n1             |          0 |
| p1             |          0 |
| p2             |          0 |
+----------------+------------+
But no rows in the partitions ????? */

/* However, selecting the data from the partitions shows the data */

SELECT *
FROM
    test
PARTITION (n1, p1, p2);
/*+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1990-01-01 |
|    2 | 2001-01-01 |
|    3 | 2011-01-01 |
+------+------------+*/

/* Flush tables did not help, and also closing and reopening the database */

Upvotes: 0

Views: 144

Answers (0)

Related Questions