Reputation: 2424
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