MySQL 5.7 Crash Analysis: Temporary Table Bug, Reproduction with MTR, and Mitigation Strategies

Background : In a MySQL 5.7.30 master‑slave read/write split setup, the replica crashed with an InnoDB assertion failure at btr0btr.cc line 2165 . System monitoring showed normal CPU/memory usage but a slight I/O wait, while slow‑log revealed temporary table and file‑sort queries.

Fault Analysis : The crash is linked to bug #101154 , where btr_insert_on_non_leaf_level_func() writes to a temporary table and triggers an assertion err == DB_SUCCESS . The bug exists only in MySQL 5.7.

0x7f2dd49d0700  InnoDB: Assertion failure in thread 139834817316608 in file btr0btr.cc line 2165
InnoDB: Failing assertion: err == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

The file btr0btr.cc handles B‑tree modifications; line 2165 checks available space on each level during non‑leaf inserts. If a leaf split has already started, the operation cannot be rolled back, leading to a crash.

Parameter Inspection :

innodb_temp_data_file_path : ibtmp1:12M:autoextend:max:20G

tmp_table_size / max_heap_table_size : 64M (effective limit is the smaller of the two)

internal_tmp_disk_storage_engine : InnoDB (default)

created_tmp_disk_tables : 2987733 , created_tmp_tables : 11049848

High numbers of temporary tables indicate heavy use of disk‑based temp tables; increasing tmp_table_size and max_heap_table_size can alleviate pressure.

Problem Reproduction : Using the MySQL Test Run (MTR) framework, a test case my0420.test was created. The key parameter innodb_limit_optimistic_insert_debug limits records per B‑tree page, forcing page splits.

innodb_limit_optimistic_insert_debug
# install dependencies
yum install -y gcc gcc-c++ cmake ncurses ncurses-devel bison openssl openssl-devel
# compile MySQL 5.7.30 with debug support
cmake . -DCMAKE_INSTALL_PREFIX=/tools/mysql-test5.7.30 \
      -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci \
      -DWITH_DEBUG=1 -DWITH_BOOST=boost
make && make install
# test script (my0420.test)
SET GLOBAL innodb_limit_optimistic_insert_debug=4;
while ($i <= 15) {
  INSERT INTO t1 SELECT * FROM t1;
}
SELECT COUNT(*) FROM t1;
SET GLOBAL innodb_limit_optimistic_insert_debug=2;
SELECT * FROM t1 ORDER BY func1(a);

Adding --internal_tmp_disk_storage_engine=MYISAM or increasing innodb_temp_data_file_path to a larger max size prevented the crash in the test environment.

Test Logs : MTR execution showed the server aborting with signal 6 and the same assertion at btr0btr.cc line 2165 . The production error differed (invalid pointer) but pointed to similar temporary‑table pressure.

2022-04-20 15:05:37 InnoDB: Assertion failure in thread 140473762858752 in file btr0btr.cc line 2165
InnoDB: Failing assertion: err == DB_SUCCESS
...

Conclusions :

The bug appears only in MySQL 5.7; upgrading to 8.0 eliminates it.

Increasing innodb_temp_data_file_path max size reduces crash probability when SQL cannot be optimized.

Setting internal_tmp_disk_storage_engine=MYISAM avoids the crash, though the default InnoDB is usually preferred.

Recommended Change Order :

Optimize SQL to reduce temporary‑table usage.

Increase innodb_temp_data_file_path max size.

Upgrade to MySQL 8.0 (session‑based temporary tables).

Consider changing internal_tmp_disk_storage_engine to MYISAM if needed.

References :

Bug report: https://bugs.mysql.com/bug.php?id=101154

Percona JIRA: https://jira.percona.com/browse/PS-7318

MySQL 5.7 documentation on server variables and temporary tables.

MTR framework documentation: https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_MYSQLTEST_FRAMEWORK_COMPONENTS.html