Bug #121742 Contribution by ByteDance RDS MySQL team:innoDB shutdown crash after duplicate-key failure during multi-index online DDL

Submitted: 11 Nov 9:51 Modified: 11 Nov 10:00
Reporter: alex xing (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.4.7 OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 9:51] alex xing
Description:
innoDB may crash during normal shutdown after an online DDL fails while building multiple indexes.

The failure occurs when:
1. A table with an existing primary key is rebuilt.
2. The clustered index builder releases a B-tree page and leaves it buffer-fixed.
3. A later unique secondary index build fails with`ER_DUP_ENTRY` .
4. The DDL cleanup deletes the builders without calling`Btree_load::latch()` and`Btree_load::finish()` .

The page remains in the buffer pool with:
page_type=FIL_PAGE_INDEX
was_stale=1
buf_fix_count=1
io_fix=BUF_IO_NONE
is_dirty=0

with`innodb_fast_shutdown=1` , a subsequent normal shutdown reaches`buf_assert_all_are_replaceable()` and crashes:
[InnoDB] [FATAL] Page still fixed or dirty at shutdown.
[InnoDB] Assertion failure: buf0buf.cc:1350

This is reproducible in a release build. 

How to repeat:
--echo # Rebuild clustered index and fail a later unique secondary index build.
set  innodb_ddl_buffer_size=1048576;
CREATE TABLE t1 (
  c1 INT NOT NULL PRIMARY KEY,
  c2 INT NOT NULL
) ENGINE=InnoDB;

--disable_query_log
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 SELECT c1 + 1, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 2, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 4, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 8, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 16, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 32, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 64, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 128, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 256, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 512, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 1024, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 2048, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 4096, 1 FROM t1;
INSERT INTO t1 SELECT c1 + 8192, 1 FROM t1;
--enable_query_log

--error ER_DUP_ENTRY
ALTER TABLE t1 ENGINE=InnoDB, ADD UNIQUE KEY uk_c2(c2), ALGORITHM=INPLACE;

--echo # Pages still buffer fixed should assert during shutdown.
--source include/shutdown_mysqld.inc
--source include/start_mysqld.inc

DROP TABLE t1;

Suggested fix:
Add an explicit cleanup method to`ddl::Builder` that reuses the existing error cleanup logic:
void Builder::cleanup_btr_load() noexcept {
  if (m_btr_load == nullptr) {
    return;
  }

  auto err = get_error();
  if (err == DB_SUCCESS) {
    err = DB_ERROR;
  }

  static_cast<void>(handle_error(err));
}

Add a`Loader` helper to clean up every builder:
void Loader::cleanup_builders() noexcept {
  for (auto builder : m_builders) {
    builder->cleanup_btr_load();
  }
}

Call it from the error path in`Loader::build_all()` before returning:
if (err != DB_SUCCESS) {
  m_ctx.set_error(err);
  cleanup_builders();
}

This ensures that every pending`Btree_load` releases its buffer fixes through`latch()` and`finish()` before the failed DDL tablespace cleanup and server shutdown.
[11 Nov 9:54] alex xing
a simple patch to describe the bugfix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-community-bug-ddl-bulk-load-cleanup.patch (application/octet-stream, text), 4.39 KiB.

[11 Nov 10:00] alex xing
Additional impact during normal operation:

The shutdown crash is the most visible symptom, but the leaked buffer fix and
bulk-load resources can also affect a running server:

1. Effective buffer pool capacity reduction

   The stale page remains buffer-fixed and cannot be evicted or replaced by the
   LRU subsystem. Repeated occurrences can reduce the effective buffer pool
   capacity. Under buffer pool pressure, page allocation and read operations
   may wait longer. This may increase metrics such as
   Innodb_buffer_pool_wait_free, although that metric alone cannot identify
   this specific root cause.

2. Buffer pool resize or shrink failures

   If the pinned stale page belongs to a buffer pool chunk that needs to be
   removed, the resize or shrink operation cannot move the page out of the
   chunk. The resize operation may therefore fail or repeatedly retry.

3. Memory growth outside the buffer pool

   The failed cleanup path bypasses the normal B-tree bulk-load finalization.
   Page-load objects and their private heaps may remain allocated after the
   DDL has failed. Repeated failed online DDL operations can therefore cause
   memory usage to grow outside the buffer pool.

4. Delayed failure

   The client still receives the expected duplicate-key error from the DDL.
   The leaked resources may remain unnoticed while the server continues
   running. The problem is usually exposed later during normal shutdown, when
   InnoDB checks that all pages are replaceable.

This is a resource-lifetime problem. It does not change the uniqueness
constraint result: the online DDL correctly reports the duplicate-key error.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.