Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4
-
None
Description
EXCHANGE/CONVERT ... WITHOUT VALIDATION are supposed to bypass the check that each row belongs to the partition in question. However as a side-effect it also allows to bypass unique constraints, and it seems there is no good way to repair it. REPAIR TABLE, REPAIR PARTITION, REBUILD PARTITION don't help. REPAIR TABLE upon the failed attempt points at the duplicate key value suggesting to handle it manually, but since it only shows one violation at a time and there can be thousands or millions such records after unfortunate alter without validation, it is not really a feasible way to recover. Dump/reload which CHECK suggests also doesn't work as it simply tries to re-insert duplicate records into the table.
Note: REBUILD PARTITION fails with different errors on a release build and with an assertion failure on debug builds (similar to MDEV-20498), which is why it is commented in the test case below.
--source include/have_partition.inc
|
|
|
create table t1 (a int unique) partition by range(a) ( |
partition p0 values less than (100), |
partition p1 values less than (maxvalue) |
);
|
insert into t1 values (1),(2),(3); |
|
|
create table t (a int unique); |
insert into t values (1),(3); |
|
|
alter table t1 exchange partition p1 with table t without validation; |
check table t1 extended; |
repair table t1; |
alter table t1 repair partition p1; |
#alter table t1 rebuild partition p1; |
check table t1 extended; |
select * from t1; |
|
|
# Cleanup
|
drop table if exists t1, t; |
|
11.4 5462b61b0c3ae59d3996018d7da34220460baf46 |
alter table t1 exchange partition p1 with table t without validation; |
check table t1 extended; |
Table Op Msg_type Msg_text |
test.t1 check error Found a misplaced row |
test.t1 check error Partition p1 returned error |
test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! |
repair table t1; |
Table Op Msg_type Msg_text |
test.t1 repair error Failed to move/insert a row from part 1 into part 0: |
Duplicate key found, please update or delete the record: |
a:1
|
test.t1 repair error Partition p1 returned error
|
test.t1 repair error Corrupt
|
alter table t1 repair partition p1; |
Table Op Msg_type Msg_text |
test.t1 repair error Failed to move/insert a row from part 1 into part 0: |
Duplicate key found, please update or delete the record: |
a:1
|
test.t1 repair error Partition p1 returned error
|
test.t1 repair error Corrupt
|
check table t1 extended; |
Table Op Msg_type Msg_text |
test.t1 check error Found a misplaced row |
test.t1 check error Partition p1 returned error |
test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! |
select * from t1; |
a
|
1
|
1
|
2
|
3
|
3
|
MySQL 8.2 doesn't look any better, except for maybe the assertion failure.
Attachments
Issue Links
- is caused by
-
MDEV-22164 WITHOUT VALIDATION for EXCHANGE PARTITION/CONVERT IN
-
- Closed
-