Why MySQL Crashes on TempTable Index Scans and How MySQL 8.0.30 Fixed It

Appendix

After the previous article, we continue to investigate the underlying principles of MySQL core modules.

1. Introduction

A customer's online environment runs MySQL version 8.0.28. A complex SQL statement uses an internal temporary table, and because it hits a bug in the TempTable storage engine, MySQL crashes.

The bug was fixed in MySQL 8.0.30, which means versions 8.0.29 and earlier contain this issue.

2. Problem Reproduction

2.1 SQL Statement

with t_mes_post_data as (select t1.* from tbl_01 t1 where t1.sh_year = "2025" and t1.sh_month = "02" and t1.eq_type = 'N' and t1.del_flag != '1'),
 k1 as (select t1.*,t2.plant,t2.mv_price,t2.price_unit from tbl_02 t1 inner join tbl_03 t2 on t1.sh_year =t2.sh_year and t1.sh_month = t2.sh_month and t1.comp =t2.material_id where t1.sh_year ="2025" and t1.sh_month = "02" and t1.comp_qty > 0),
 k2 as (select t1.*,prod_unit,comp,comp_qty,comp_unit,base_qty,plant,mv_price,price_unit from t_mes_post_data t1 inner join k1 on concat_ws('_',t1.prod_id,t1.work_center_id) = k1.mapping_key and t1.sh_year =k1.sh_year and t1.sh_month =k1.sh_month where lot_type ='SC' and t1.sh_year ="2025" and t1.sh_month = "02" and t1.data_from='M1' and left(k1.work_center_id,3)='J02' and k1.plant='PJ02'
 union all
 select t1.*,prod_unit,comp,comp_qty,comp_unit,base_qty,plant,mv_price,price_unit from t_mes_post_data t1 inner join k1 on concat_ws('_',t1.prod_id,t1.work_center_id) = k1.mapping_key and t1.sh_year =k1.sh_year and t1.sh_month =k1.sh_month where lot_type ='SC' and t1.sh_year ="2025" and t1.sh_month = "02" and t1.data_from != 'M1' and left(k1.work_center_id,3) !='J02' and k1.plant='PJ01'),
 k2_1 as (select prod_id,comp,bu,comp_qty,base_qty,mv_price,price_unit from k2 group by prod_id,comp,bu,comp_qty,base_qty,mv_price,price_unit),
 k2_2 as (select work_center_id ,sum(qty) as work_total_qty from k2 group by work_center_id),
 k3 as (select work_center_id as work_center,comp,mv_price,price_unit from k2 where comp not in (select material_id from tbl_04) group by work_center_id,comp,mv_price,price_unit),
 k4 as (select k2.work_center_id as work_center,comp,round(sum((comp_qty/base_qty)*qty)*10000/k2_2.work_total_qty,10) as comp_qty, 10000 as base_qty from k2 inner join k2_2 on k2.work_center_id=k2_2.work_center_id group by k2.work_center_id,comp),
 k4_1 as (select k3.work_center,k3.comp,k3.mv_price,k3.price_unit,k4.comp_qty,k4.base_qty from k3 left join k4 on k3.work_center=k4.work_center and k3.comp=k4.comp)
select * from k4_1 where work_center ='J02' and comp='0001';

2.2 Crash Stack

std::__1::vector<temptable::Table::Index_entry, temptable::Allocator<temptable::Table::Index_entry, temptable::Allocation_scheme<temptable::Exponential_policy, temptable::Prefer_RAM_over_MMAP_policy_obeying_per_table_limit>>>::size() const
temptable::Table::number_of_indexes() const (storage/temptable/include/temptable/table.h:181)
temptable::Handler::index_init(unsigned int, bool) (storage/temptable/src/handler.cc:345)
handler::ha_index_init(unsigned int, bool) (sql/handler.cc:2902)
IndexScanIterator<false>::Init() (sql/iterators/basic_row_iterators.cc:89)
AggregateIterator::Init() (sql/iterators/composite_iterators.cc:192)
MaterializeIterator::MaterializeQueryBlock(...) (sql/iterators/composite_iterators.cc:845)
...

From the stack we see that Table::number_of_indexes() calls m_index_entries.size(), which leads to a segmentation fault because this points to an invalid address (0x0000000000000078).

The m_index_entries attribute belongs to Table. Its size() method crashes, indicating that m_index_entries itself points to an invalid address.

2.3 Problem Localization

inline size_t Table::number_of_indexes() const { return m_index_entries.size(); }

The call chain shows that Handler::index_init() invokes m_opened_table->number_of_indexes(). In this case m_opened_table is nullptr, so calling number_of_indexes() dereferences a null pointer and crashes MySQL.

Note: The MaterializeIterator::Init() method opens an internal temporary table, but the table accessed later in the crash is a different temporary table that was never opened.

3. Further Analysis

The root cause is that m_opened_table is nullptr. We need to understand why it is null.

When a SELECT statement uses an internal temporary table (storage engine TempTable), MaterializeIterator::Init() eventually calls temptable::Handler::open():

int Handler::open(const char *table_name, int, uint, const dd::Table *) { ... Result ret; try { auto &kv_store = kv_store_shard[thd_thread_id(ha_thd())]; m_opened_table = kv_store.find(table_name); if (m_opened_table) { ret = Result::OK; opened_table_validate(); } else { ret = Result::NO_SUCH_TABLE; } } catch (std::bad_alloc &) { ret = Result::OUT_OF_MEM; } ... DBUG_RET(ret); }

If kv_store.find(table_name) fails, m_opened_table remains nullptr, and subsequent calls to its methods cause the crash.

In handler::ha_index_init() the code is:

int handler::ha_index_init(uint idx, bool sorted) { int result; if (!(result = index_init(idx, sorted))) inited = INDEX; mrr_have_range = false; end_range = nullptr; return result; }

The index_init() call is actually Handler::index_init(), whose source is:

int Handler::index_init(uint index_no, bool) { DBUG_TRACE; opened_table_validate(); Result ret; if (index_no >= m_opened_table->number_of_indexes()) { ret = Result::WRONG_INDEX; } else { handler::active_index = index_no; ret = Result::OK; } }

Because m_opened_table is nullptr, the expression m_opened_table->number_of_indexes() dereferences a null pointer, leading to the crash.

4. Problem Fix

The issue occurs because the TempTable engine does not support covering index scans. The optimizer should have prevented a covering‑index plan for internal temporary tables, but a bug allowed it.

MySQL 8.0.30 adds extra checks to skip covering index scans for temporary tables. The relevant commit (b88d49d37150917a4ba1fbcd97c2424d31f5bc00) modifies test_if_skip_sort_order():

/* 1 */ if (!tab->range_scan() || tab->range_scan() == save_range_scan) // created no QUICK
{
    /* 4 */ assert(tab->range_scan() == save_range_scan || tab->range_scan() == nullptr);
    /* 5 */ tab->set_range_scan(nullptr);
    /* 6 */ tab->set_index(best_key);
    /* 7 */ tab->set_type(JT_INDEX_SCAN); // Read with index_first(), index_next()
    ...
}

In 8.0.30 the condition becomes:

if (!(is_temporary_table(tab->table_ref) && tab->table_ref->table->s->db_type() == temptable_hton) && (!tab->range_scan() || tab->range_scan() == save_range_scan)) {
    assert(tab->range_scan() == save_range_scan || tab->range_scan() == nullptr);
    tab->set_range_scan(nullptr);
    tab->set_index(best_key);
    tab->set_type(JT_INDEX_SCAN);
    ...
}

The added checks exclude internal temporary tables, preventing the optimizer from choosing a covering index scan and eliminating the crash.

5. Table Structures and Data

5.1 Table Definitions

CREATE TABLE `tbl_01` (
  `sh_year` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `sh_month` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `lot_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `prod_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `fab` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `qty` bigint DEFAULT NULL,
  `eq_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `lot_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `tracktimes` decimal(10,2) DEFAULT NULL,
  `data_from` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `gross_die` int DEFAULT NULL,
  `lot_status` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `lot_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0',
  `next_prod` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `shiprequirement` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `reelid` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `waferid` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `lot_qty` int unsigned DEFAULT NULL,
  `lotid_item` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `work_center_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `eq_type` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `bu` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `area` decimal(10,2) DEFAULT NULL,
  `total_area` decimal(10,2) DEFAULT NULL,
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0',
  KEY `tbl_01_sh_year_IDX` (`sh_year`,`sh_month`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `tbl_02` (... same style ...);
CREATE TABLE `tbl_03` (... same style ...);
CREATE TABLE `tbl_04` (`material_id` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

5.2 Sample Data Insertion

INSERT INTO `tbl_04` VALUES ('dignissimos deserunt error quisquam hic.'),('aliquid placeat porro sit omnis laboriosam.'), ... ;
INSERT INTO `tbl_02` VALUES ('2025','02','earum quam quas quod itaque error ut totam itaque.','adipisci animi ullam sed vel voluptatem.','J02','0001',18762531.000,'dolores distinctio error consequatur necessitatibu',1.99,'001_J02'), ... ;
INSERT INTO `tbl_03` VALUES ('2025','02','0001','PJ02',4.23,0.66,'001_J02'), ... ;
INSERT INTO `tbl_01` VALUES ('2025','02','quam nam eius voluptates perferendis ea.','001','autem et vel asperiores aut mollitia ea temporibus',5577006791947779410,'vitae laboriosam maiores voluptas corrupti ipsum.','SC',4.70,'M1',1801160058,'Frank Dean','Mrs. Ms. Miss Lori F','vel quis iste aliquid eaque impedit et!','reprehenderit explicabo numquam ducimus nulla quia','Mrs. Ms. Miss Annie ','Catherine Kim',914091476,'0'), ... ;

The lengthy DDL and DML are provided so readers can reproduce the issue in their own environments.

For more details, see the previous article: MySQL Execution Plan "Lie": Why a Covered Index Still Falls Back to the Table?