Details
-
Bug
-
Status: In Progress (View Workflow)
-
Critical
-
Resolution: Unresolved
-
N/A
-
None
-
Q1/2026 Server Maintenance
Description
As marko noticed and tests confirm, the atomic CREATE OR REPLACE ... SELECT blocks InnoDB purge for the whole duration of the query. I don't know if there is such a requirement that it mustn't, but as I understand, marko considers it to be a problem, so I'm filing it.
Here is a demo in MTR format. It shouldn't go to the regression suite of course.
--source include/have_sequence.inc
|
--source include/have_innodb.inc
|
|
|
--connect (con1,localhost,root,,)
|
# This is to create some activity worth purging |
--delimiter $
|
CREATE TABLE x (id INT PRIMARY KEY, val TEXT) ENGINE=InnoDB $ |
--send
|
LOOP
|
START TRANSACTION; |
INSERT INTO x VALUES (1, 'abc'); |
DELETE FROM x WHERE id = 1; |
COMMIT; |
END LOOP $ |
--delimiter ;
|
|
|
--connection default
|
CREATE TABLE cor (a INT) ENGINE=InnoDB; |
--send CREATE OR REPLACE TABLE cor ENGINE=InnoDB AS SELECT seq FROM seq_1_to_10000000
|
|
|
--connect (con2,localhost,root,,)
|
# This is a monitor |
--disable_query_log
|
while (1)
|
{
|
--let $status= `show engine innodb status` |
--eval SELECT SUBSTRING("$status", LOCATE("Purge done for trx's n:o", "$status"), 85) AS st |
--sleep 1 |
}
|
|
bb-main-monty 6cb6e8285c50a0bbcb2b599535b8d02b7bba5256 |
Purge done for trx's n:o < 32 undo n:o < 1 state: running
|
History list length 4936
|
st
|
Purge done for trx's n:o < 32 undo n:o < 1 state: running
|
History list length 8240
|
...
|
...
|
...
|
Purge done for trx's n:o < 32 undo n:o < 1 state: running
|
History list length 532453
|
The above is with the default drop_before_create_or_replace=OFF. With drop_before_create_or_replace=ON the purge keeps happening.
Attachments
Issue Links
- blocks
-
-
- Open
-
- is caused by
-
MDEV-25292 Atomic CREATE OR REPLACE TABLE
-
- In Testing
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Stalled
-