Description:
Hello,
we noticed that in version 8.0.27 the behavior of the "drop partition" is locking the all table during the dropping while in version 5.7 we didn't see this behavior
we also noticed that drop partition take long time to finish (more then 1 minute) on empty partition after we performed: truncate/exchange
in additional to this, we can see on our monitor, that during the drop partition, its also writes to "handler_delete" which is very strange behavior because we are preforming a "drop" not a delete" (cannot rep-reduced on v5.7)
related to bugs:
https://bugs.mysql.com/bug.php?id=83435
https://bugs.mysql.com/bug.php?id=94610
How to repeat:
1. need to run load against the table
2 create the table:
CREATE TABLE `test_partition` (
`id` bigint(20) unsigned zerofill NOT NULL,
`business` varchar(64) NOT NULL DEFAULT '',
`application` varchar(32) NOT NULL DEFAULT '',
`remote_addr` varchar(64) NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime NOT NULL,
`created_day` date NOT NULL DEFAULT '0000-00-00',
`blocked` varchar(128) NOT NULL,
PRIMARY KEY (`id`,`created_day`),
KEY `created_at_indx` (`created_at`),
KEY `remote_addr_INDEX` (`remote_addr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/*!50500 PARTITION BY RANGE COLUMNS(created_day)
(PARTITION p20220810 VALUES LESS THAN ('2022-08-10') ENGINE = InnoDB,
PARTITION p20220811 VALUES LESS THAN ('2022-08-11') ENGINE = InnoDB,
PARTITION p20220812 VALUES LESS THAN ('2022-08-12') ENGINE = InnoDB,
PARTITION p20220813 VALUES LESS THAN ('2022-08-13') ENGINE = InnoDB,
PARTITION p20220814 VALUES LESS THAN ('2022-08-14') ENGINE = InnoDB,
PARTITION p20220815 VALUES LESS THAN ('2022-08-15') ENGINE = InnoDB,
PARTITION p20220816 VALUES LESS THAN ('2022-08-16') ENGINE = InnoDB,
PARTITION p20220817 VALUES LESS THAN ('2022-08-17') ENGINE = InnoDB,
PARTITION p20220818 VALUES LESS THAN ('2022-08-18') ENGINE = InnoDB,
PARTITION p20220819 VALUES LESS THAN ('2022-08-19') ENGINE = InnoDB,
PARTITION p20220820 VALUES LESS THAN ('2022-08-20') ENGINE = InnoDB,
PARTITION p20220821 VALUES LESS THAN ('2022-08-21') ENGINE = InnoDB,
PARTITION p20220822 VALUES LESS THAN ('2022-08-22') ENGINE = InnoDB,
PARTITION p20220823 VALUES LESS THAN ('2022-08-23') ENGINE = InnoDB,
PARTITION p20220824 VALUES LESS THAN ('2022-08-24') ENGINE = InnoDB,
PARTITION pMax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
3. drop the partition during load on the table
alter table test_partition drop partition test_partition;
