Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
Description
LIMIT partition temporarily populated in transaction is not used anymore after ROLLBACK
Note: There can be a race condition/non-determinism during MTR execution of the test case, as statistics on an InnoDB table is not precise (described in not-a-bug MDEV-23640). I can't put ANALYZE in the test case, as it terminates the transaction. For reproducing, just re-run or put sleeps if necessary. For a regression suite, something smarter would need to be done to maintain determinism.
If we have several empty (not full) LIMIT partitions, rows are put in the first one first, when it's overflows then next, etc – that's the expected behavior.
Same happens when partition population happens inside a transaction.
However if the transaction is rolled back and partitions again become empty, the orderly population does not happen anymore – partitions which were once (over)filled remain ignored, and rows are put into the next ones.
It affects both explicit and implicit (including auto-created) partitions. The test case below is for explicit ones, to be applicable to all versions which support system versioning.
--source include/have_partition.inc
|
--source include/have_sequence.inc
|
--source include/have_innodb.inc
|
|
|
create or replace table t1 (pk int primary key) with system versioning |
partition by system_time limit 100 ( |
partition p0 history,
|
partition p1 history,
|
partition pn current); |
insert into t1 select seq from seq_1_to_90; |
|
start transaction; |
# Puts 80 rows into p0 |
replace into t1 select seq from seq_1_to_80; |
# Puts another 70 rows into p0 |
replace into t1 select seq from seq_1_to_70; |
# Puts 60 rows into p1 |
replace into t1 select seq from seq_1_to_60; |
|
select partition_name, table_rows |
from information_schema.partitions |
where table_name = 't1'; |
rollback; |
|
select partition_name, table_rows |
from information_schema.partitions |
where table_name = 't1'; |
|
# Should put 10 rows into the empty partition p0, |
# but instead they go to p1 |
replace into t1 select seq from seq_1_to_10; |
select partition_name, table_rows |
from information_schema.partitions |
where table_name = 't1'; |
# Cleanup
|
drop table t1; |
|
10.3 4d412e98 |
replace into t1 select seq from seq_1_to_80; |
replace into t1 select seq from seq_1_to_70; |
replace into t1 select seq from seq_1_to_60; |
select partition_name, table_rows from information_schema.partitions where table_name = 't1'; |
partition_name table_rows
|
p0 150
|
p1 60
|
pn 90
|
rollback; |
select partition_name, table_rows from information_schema.partitions where table_name = 't1'; |
partition_name table_rows
|
p0 0
|
p1 0
|
pn 90
|
replace into t1 select seq from seq_1_to_10; |
select partition_name, table_rows from information_schema.partitions where table_name = 't1'; |
partition_name table_rows
|
p0 0
|
p1 10
|
pn 90
|