Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.37-galera, 5.5.38
-
Ubuntu 14.04 LTS x86_64
-
5.5.44
Description
MariaDB 5.5.38-galera, from MariaDB-hosted apt repo.
After an "ALTER TABLE ... TRUNCATE PARTITION" operation on a LIST partition, a query against the table returns data which has been removed:
CREATE TABLE `test` (
|
`uniqueId` INT NOT NULL,
|
`partitionId` INT NOT NULL,
|
PRIMARY KEY (`uniqueId`,`partitionId`)
|
) ENGINE=InnoDB PARTITION BY LIST (partitionId) (
|
PARTITION p01 VALUES IN (1),
|
PARTITION p02 VALUES IN (2)
|
);
|
|
|
INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2);
|
|
|
SELECT * FROM `test`;
|
-- Returns 1 row
|
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
|
-- Confirms 1 row in partition 'p02'
|
ALTER TABLE `test` TRUNCATE PARTITION `p02`;
|
-- Query OK
|
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test';
|
-- Both zero
|
SELECT * FROM `test`;
|
-- Still returns 1 row(!!)
|
SELECT SQL_CACHE * FROM `test`;
|
-- Correctly returns zero rows
|
SELECT SQL_NO_CACHE * FROM `test`;
|
-- Correctly returns zero rows
|
A colleague raised this issue with me, and I have been able to reproduce by the above method. When I tried with a RANGE partition rather than a LIST partition, I then saw the correct results whereas my colleague still reports seeing the same incorrect behaviour as above, with the different partitioning method. This may suggest that the issue is non-deterministic.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
MariaDB 5.5.38-galera, from MariaDB-hosted apt repo. After an "ALTER TABLE ... TRUNCATE PARTITION" operation on a LIST partition, a query against the table returns data which has been removed: CREATE TABLE `test` ( `uniqueId` INT NOT NULL, `partitionId` INT NOT NULL, PRIMARY KEY (`uniqueId`,`partitionId`) ) ENGINE=InnoDB PARTITION BY LIST (partitionId) ( PARTITION p01 VALUES IN (1), PARTITION p02 VALUES IN (2) ); INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2); SELECT * FROM `test`; -- Returns 1 row SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test'; -- Confirms 1 row in partition 'p02' ALTER TABLE `test` TRUNCATE PARTITION `p02`; -- Query OK SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test'; -- Both zero SELECT * FROM `test`; -- Still returns 1 row(!!) SELECT SQL_CACHE * FROM `test`; -- Correctly returns zero rows SELECT SQL_NO_CACHE * FROM `test`; -- Correctly returns zero rows A colleague raised this issue with me, and I have been able to reproduce by the above method. When I tried with a RANGE partition rather than a LIST partition, I then got the correct results whereas my colleague still reports seeing the same incorrect behaviour as above, with the different partitioning method. This may suggest that the issue is non-deterministic. |
MariaDB 5.5.38-galera, from MariaDB-hosted apt repo. After an "{{ALTER TABLE ... TRUNCATE PARTITION}}" operation on a {{LIST}} partition, a query against the table returns data which has been removed: {noformat} CREATE TABLE `test` ( `uniqueId` INT NOT NULL, `partitionId` INT NOT NULL, PRIMARY KEY (`uniqueId`,`partitionId`) ) ENGINE=InnoDB PARTITION BY LIST (partitionId) ( PARTITION p01 VALUES IN (1), PARTITION p02 VALUES IN (2) ); INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2); SELECT * FROM `test`; -- Returns 1 row SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test'; -- Confirms 1 row in partition 'p02' ALTER TABLE `test` TRUNCATE PARTITION `p02`; -- Query OK SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test'; -- Both zero SELECT * FROM `test`; -- Still returns 1 row(!!) SELECT SQL_CACHE * FROM `test`; -- Correctly returns zero rows SELECT SQL_NO_CACHE * FROM `test`; -- Correctly returns zero rows {noformat} A colleague raised this issue with me, and I have been able to reproduce by the above method. When I tried with a {{RANGE}} partition rather than a {{LIST}} partition, I then saw the correct results whereas my colleague still reports seeing the same incorrect behaviour as above, with the different partitioning method. This may suggest that the issue is non-deterministic. |
| Assignee | Elena Stepanova [ elenst ] |
| Fix Version/s | 5.5 [ 15800 ] | |
| Assignee | Elena Stepanova [ elenst ] | Oleksandr Byelkin [ sanja ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Workflow | MariaDB v2 [ 51304 ] | MariaDB v3 [ 67125 ] |
| Sprint | Sprint 1 [ 4 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Rank | Ranked higher |
| Assignee | Vicentiu Ciorbaru [ cvicentiu ] | Oleksandr Byelkin [ sanja ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Component/s | Query Cache [ 10120 ] | |
| Fix Version/s | 5.5.44 [ 19100 ] | |
| Fix Version/s | 5.5 [ 15800 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 67125 ] | MariaDB v4 [ 148074 ] |
Reproducible as described on the current 5.5 tree.
Could not reproduce on 10.0 on whatever reason, but it doesn't mean it's actually fixed in 10.0.
Could not reproduce on MySQL, possibly due to this radical change: http://bugs.mysql.com/bug.php?id=53775 .