[MDEV-33128] WITHOUT VALIDATION allows to insert duplicate values into unique key without a way to repair Created: 2023-12-27  Updated: 2024-01-04

Status: Open
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 11.4
Fix Version/s: 11.4

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-22164 WITHOUT VALIDATION for EXCHANGE PARTI... Closed

 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.

 Comments   
Comment by Elena Stepanova [ 2023-12-27 ]

I didn't make it a "blocker" for MDEV-22164 feature, as I'm not sure whether anything can be done about it. MySQL behaves in the same way, but it's unclear whether it's the expected outcome or an omission, at least MySQL manual doesn't say anything about it (and usually, if it were a known effect, it would have).

Comment by Sergei Golubchik [ 2023-12-27 ]

sure, there is a way to repair the table. something like

delete from t1 where a in (select a from t1 group by a having count(a) > 1)

but let's say REPAIR TABLE should be able to repair the table too

Comment by Elena Stepanova [ 2023-12-27 ]

sure, there is a way to repair the table. something like

delete from t1 where a in (select a from t1 group by a having count(a) > 1)

Sure, when I said "no way to repair", I meant a meaningful way with a predictable result and preferably with minimal data loss. Otherwise there are other ways – one can truncate the table, or drop the newly added partition, etc.
For the above query and its likes, as the table is broken, the behavior is undefined. It may delete all rows with these keys (not just duplicates), or, if one's more lucky, it may delete the old good records and preserve the new not-so-good ones. With the data from the description, for InnoDB it does the former and for MyISAM the latter, but I suppose it can depend on many factors.

Comment by Elena Stepanova [ 2024-01-04 ]

It seems there is a more or less universal (although not immediately obvious) way to fix a variety of problems with such tables, including the one describe in this ticket: ALTER IGNORE TABLE ... FORCE, ALGORITHM=COPY.
The table gets rebuilt and misplaced rows are moved around, while IGNORE ensures that the operation doesn't fail on duplicate keys, and duplicate entries are removed instead.
Algorithm is important for InnoDB, as otherwise it's inplace and doesn't do much.

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;
 
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!
 
alter ignore table t1 force, algorithm=copy;
 
check table t1 extended;
 
Table	Op	Msg_type	Msg_text
 
test.t1	check	status	OK
 
select * from t1;
 
a
 
1
 
2
 
3
 
select * from t1 partition (p0);
 
a
 
1
 
2
 
3
 
select * from t1 partition (p1);
 
a

Generated at Thu Feb 08 10:36:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.