Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6(EOL), 10.11, 11.4, 11.8, 12.3, 13.0
Description
A join whose derived table is materialized into an in-memory temporary table that then runs out of memory aborts a debug build in handler::ha_close().
How to repeat
CREATE TABLE t (v VARCHAR(1024)); |
INSERT INTO t SELECT CONCAT('v', LPAD(seq, 6, '0')) FROM seq_1_to_100; |
|
|
SET SESSION tmp_table_size=262144, max_heap_table_size=262144; |
|
|
SELECT COUNT(*) FROM t a JOIN (SELECT DISTINCT v FROM t) d ON a.v = d.v; |
|
|
DROP TABLE t; |
The temporary table sizes only have to be small enough that the derived table does not fit in memory. They are set explicitly here so that the test does not depend on the server defaults. A derived table that uses GROUP BY in place of DISTINCT fails in the same way.
Result
Debug build of main at a1b3f980ddd:
mariadbd: sql/handler.cc:4009: int handler::ha_close(): Assertion `m_psi_batch_mode == PSI_BATCH_MODE_NONE' failed.
|
|
|
sql/handler.cc:4010(handler::ha_close())
|
sql/sql_select.cc:23923(create_internal_tmp_table_from_heap(THD*, TABLE*, st_maria_columndef*, st_maria_columndef**, int, bool, bool*))
|
sql/sql_union.cc:431(select_unit::write_record())
|
sql/sql_union.cc:161(select_unit::send_data(List<Item>&))
|
sql/sql_class.cc:3331(select_result_sink::send_data_with_check(List<Item>&, st_select_lex_unit*, unsigned long long))
|
sql/sql_select.cc:26149(end_send(JOIN*, st_join_table*, bool))
|
sql/sql_select.cc:24983(evaluate_join_record(JOIN*, st_join_table*, int))
|
sql/sql_select.cc:34009(AGGR_OP::end_send())
|
sql/sql_select.cc:24428(sub_select_postjoin_aggr(JOIN*, st_join_table*, bool))
|
sql/sql_select.cc:24683(sub_select(JOIN*, st_join_table*, bool))
|
sql/sql_select.cc:24263(do_select(JOIN*, Procedure*))
|
sql/sql_select.cc:5126(JOIN::exec_inner())
|
sql/sql_select.cc:4914(JOIN::exec())
|
sql/sql_select.cc:5440(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))
|
sql/sql_derived.cc:1364(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))
|
sql/sql_derived.cc:235(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))
|
sql/sql_select.cc:17074(st_join_table::preread_init())
|
sql/sql_select.cc:25809(join_init_read_record(st_join_table*))
|
sql/sql_join_cache.cc:3503(JOIN_TAB_SCAN::open())
|
sql/sql_join_cache.cc:2359(JOIN_CACHE::join_matching_records(bool))
|
sql/sql_join_cache.cc:2180(JOIN_CACHE::join_records(bool))
|
sql/sql_select.cc:24493(sub_select_cache(JOIN*, st_join_table*, bool))
|
sql/sql_select.cc:24683(sub_select(JOIN*, st_join_table*, bool))
|
sql/sql_select.cc:24263(do_select(JOIN*, Procedure*))
|
sql/sql_select.cc:5126(JOIN::exec_inner())
|
sql/sql_select.cc:4914(JOIN::exec())
|
sql/sql_select.cc:5440(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))
|
sql/sql_select.cc:637(handle_select(THD*, LEX*, select_result*, unsigned long long))
|
Analysis
handler::m_psi_batch_mode records whether a handler is currently collapsing its per-row Performance Schema events into a single batched event. A started batch owns an open PSI_table_locker, which end_psi_batch_mode() closes, so handler::ha_close() requires the handler to have left batch mode first:
DBUG_ASSERT(m_psi_batch_mode == PSI_BATCH_MODE_NONE);
|
DBUG_ASSERT(m_psi_locker == NULL);
|
The stack shows the two ends of the collision. JOIN_CACHE::join_records() puts the derived table's handler into batch mode around the scan of that table:
if (join_tab->cached_pfs_batch_update) |
join_tab->table->file->start_psi_batch_mode();
|
/* Find all records from join_tab that match records from join buffer */ |
rc= join_matching_records(skip_last);
|
if (join_tab->cached_pfs_batch_update) |
join_tab->table->file->end_psi_batch_mode();
|
Only inside that call does JOIN_TAB_SCAN::open() reach st_join_table::preread_init(), which materializes the derived table for the first time. The materialization overflows the in-memory temporary table, and create_internal_tmp_table_from_heap() replaces the handler of that table:
/* remove heap table and change to use myisam table */ |
(void) table->file->ha_rnd_end(); |
(void) table->file->ha_close(); // This deletes the table ! |
delete table->file; |
So the handler that was put into batch mode is closed and deleted while its batch is still open, and the matching end_psi_batch_mode() can never reach it.
The condition for entering batch mode, JOIN_TAB::pfs_batch_update(), tests the shape of the plan alone and does not consult whether the Performance Schema is enabled, so the failure does not depend on performance_schema being switched on. It was reproduced on a server with that setting left at its default.
The check is a DBUG_ASSERT, so only debug builds abort on it. A release build instead leaks the PSI_table_locker and loses the table io event for that scan, which is presumably why this has gone unnoticed.
Versions tested
Reproduced on a debug build of main at a1b3f980ddd, with the stack shown above. That build is unmodified upstream.
Reproduced identically on two further debug builds on unrelated development branches, which is only mentioned to show that the failure is not an artifact of one tree.
No lower bound was established. Older release series were not tested, so the version fields should be set from a check against them rather than from this report. The code involved has no obvious recent origin: both sites quoted above are long standing, so earlier series are worth checking.
Not a temporary table engine issue
The temporary table only has to be one that starts in memory and later converts. Create_tmp_table::choose_engine() sends the table to the on-disk engine straight away when it has blob columns, needs a unique constraint, or when tmp_memory_table_size is zero, and to the in-memory engine otherwise. The reproducer above stays on the in-memory side of that test at the point the engine is chosen, so the conversion happens later, during the materialization, which is what creates the window.
Attachments
Issue Links
- relates to
-
-
- In Review
-