| Bug #56080 | Assertion `inited==INDEX' in sql/handler.h:1534 on SELECT MAX(..) | ||
|---|---|---|---|
| Submitted: | 18 Aug 2010 1:49 | Modified: | 3 May 2011 1:01 |
| Reporter: | Elena Stepanova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.99-m5-debug | OS: | Any |
| Assigned to: | Roy Lyseng | CPU Architecture: | Any |
| Tags: | regression | ||
[18 Aug 2010 1:49]
Elena Stepanova
[18 Aug 2010 1:52]
Elena Stepanova
Sync point with context:
diff -C 3 -r a/sql/item_subselect.cc b/sql/item_subselect.cc
*** a/sql/item_subselect.cc 2010-08-18 03:29:48.000000000 +0200
--- b/sql/item_subselect.cc 2010-08-18 03:29:51.000000000 +0200
***************
*** 38,43 ****
--- 38,44 ----
#include "set_var.h"
#include "sql_select.h"
#include "sql_parse.h" // check_stack_overrun
+ #include "debug_sync.h"
inline Item * and_items(Item* cond, Item *item)
{
***************
*** 2094,2099 ****
--- 2095,2101 ----
{
DBUG_ENTER("subselect_uniquesubquery_engine::cleanup");
/* Tell handler we don't need the index anymore */
+ DEBUG_SYNC(thd,"before_index_end_in_subselect");
if (tab->table->file->inited)
tab->table->file->ha_index_end();
DBUG_VOID_RETURN;
[4 Oct 2010 8:15]
Roy Lyseng
Bug#55986 is probably a duplicate of this one. See that report for likely origin.
[19 Oct 2010 8:30]
Roy Lyseng
There seems to be a race condition, where a thread may attempt to end a table or index scan for a released TABLE object: When finishing up query execution, do_select() calls JOIN::join_free(). This again calls JOIN::cleanup(), which calls ha_index_or_rnd_end() for the subquery table, which again calls ha_index_end() for the table. Then we're calling JOIN::destroy(), which calls JOIN::cleanup() again. This is repeated for all st_select_lex objects, which means that eventually, all JOIN objects are "fully" cleaned up. Then we call close_thread_tables() from mysql_execute_command(), which releases all TABLE objects and makes them free for reuse by other threads. Then THD::cleanup_after_query() calls free_items(), which eventually calls subselect_uniquesubquery_engine::cleanup(). This object still has a TABLE reference, and if that object has been reused by another thread, the function will attempt to clean up a table or index scan on behalf of another thread.
[9 Nov 2010 15:30]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/123297 3280 Roy Lyseng 2010-11-09 Bug#56080: Assertion 'inited=INDEX in sql/handler.h The problem occurs because some TABLE objects are associated with the optimizer execution structures even after close_thread_tables() has been called. In turn, this means that final cleanup of some TABLE objects is done from within THD::cleanup_after_query(). At this point in time, the TABLE objects may already have been given to another session, meaning that we may end another session's index or table scan. The safest solution seems to be to always perform a thorough cleanup of all execution data structures, including releasing the TABLE objects, before close_thread_tables() is called. In other words, this will have to be done at the end of each execution. However, this is currently not possible in the implementation of subquery materialization. This part of the optimizer assigns some objects, including a TABLE object, for the lifetime of the statement object, and not for each execution. This gives an odd situation, where some parts of the data structures are set up for reuse and others are not. Combine this with the fact that the user may decide to use another strategy for a subsequent subquery execution, the "permanent" data structures may not even be used later. It is therefore reasonable to allocate materialization execution structures for the lifetime of one execution. It means that the functions init_permanent() and init_runtime() of class subselect_hash_sj_engine are combined into one setup() function. In addition, the function Item_subselect::cleanup() will be called when the cleanup() function for the underlying query expression is called, and this function will cleanup and destroy the materialization data structures, so that the subquery item object is ready for a new optimization process in the next round of execution. However, the EXPLAIN functionality relies on these data structures being present when explaining a query containing a materialized subquery. Thus, we have two conflicting requirements for query cleanup: - For maximum efficiency we should release resources as early as possible after execution. - We cannot release resources that are needed for EXPLAIN queries. One solution to this problem is to split resource cleanup in two: 1. Delete JOIN objects as soon as possible - this makes sense because TABLE objects are associated with them through JOIN_TAB. But notice that JOIN objects may be needed for EXPLAIN queries... 2. Cleanup query expression objects (ie st_select_lex and st_select_lex_unit) later (in practice at end of query execution). Because this bug was caused by a race condition, no simple test case has been made. mysql-test/r/union.result Change in EXPLAIN EXTENDED output that shows an optimized predicate in the two query specifications within a UNION in a subquery. AFAIK, this is similar to how a non-subquery is reported, so the change is an improvement. sql/ha_partition.cc Comment change. sql/item_subselect.cc In Item_subselect::cleanup(), "new" engine is always deleted after an execution. Item_in_subselect::setup_engine() now builds the materialization engine for one execution only. Specific arena is no longer needed. In subselect_union_engine::cleanup(), call to unit->reinit_exec_mechanism is deleted because it is redundant. Implementation of cleanup() functions for subclasses of subselect_engine has been refactored. subselect_hash_sj_engine::setup() replaces init_permanent() and init_runtime(). For class subselect_hash_sj_engine, freeing of the temporary result table is moved from the destructor to ::cleanup(). Some changes necessary because of interface changes. sql/item_subselect.h Some cleanup of the subselect_engine classes: - All virtual functions now marked virtual in all derived classes. - Functions that returned bool result are now declared accordingly. - Slight cleanup of private/protected/public performed. - Improved constructors. sql/sql_lex.h Change in friend declaration. sql/sql_select.cc Function JOIN::reinit() is renamed to JOIN::reset(). JOIN::destroy() will now delete connection to associated tables. The st_select_lex_unit object is no longer cleaned up as early as before, but instead relying on the general cleanup of lex->unit in mysql_execute_command(). sql/sql_select.h A few small interface changes. sql/sql_union.cc Cleaned up some confusing error handling in st_select_lex_unit::exec() and st_select_lex::cleanup().
[5 Jan 2011 7:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/127945 3321 Roy Lyseng 2011-01-05 Bug#56080: Assertion 'inited=INDEX in sql/handler.h The problem occurs because some TABLE objects are associated with the optimizer execution structures even after close_thread_tables() has been called. In turn, this means that final cleanup of some TABLE objects is done from within THD::cleanup_after_query(). At this point in time, the TABLE objects may already have been given to another session, meaning that we may end another session's index or table scan. The safest solution seems to be to always perform a thorough cleanup of all execution data structures, including releasing the TABLE objects, before close_thread_tables() is called. In other words, this will have to be done at the end of each execution. However, this is currently not possible in the implementation of subquery materialization. This part of the optimizer assigns some objects, including a TABLE object, for the lifetime of the statement object, and not for each execution. This gives an odd situation, where some parts of the data structures are set up for reuse and others are not. Combine this with the fact that the user may decide to use another strategy for a subsequent subquery execution, the "permanent" data structures may not even be used later. It is therefore reasonable to allocate materialization execution structures for the lifetime of one execution. It means that the functions init_permanent() and init_runtime() of class subselect_hash_sj_engine are combined into one setup() function. In addition, the function Item_subselect::cleanup() will be called when the cleanup() function for the underlying query expression is called, and this function will cleanup and destroy the materialization data structures, so that the subquery item object is ready for a new optimization process in the next round of execution. However, the EXPLAIN functionality relies on these data structures being present when explaining a query containing a materialized subquery. Thus, we have two conflicting requirements for query cleanup: - For maximum efficiency we should release resources as early as possible after execution. - We cannot release resources that are needed for EXPLAIN queries. One solution to this problem is to split resource cleanup in two: 1. Delete JOIN objects as soon as possible - this makes sense because TABLE objects are associated with them through JOIN_TAB. But notice that JOIN objects may be needed for EXPLAIN queries... 2. Cleanup query expression objects (ie st_select_lex and st_select_lex_unit) later (in practice at end of query execution). mysql-test/r/optimizer_debug_sync.result Results for new test case. mysql-test/t/optimizer_debug_sync.test Test case for bug. Requires the debug sync facility, and often fails without the bug fix. mysql-test/r/union.result Change in EXPLAIN EXTENDED output that shows an optimized predicate in the two query specifications within a UNION in a subquery. AFAIK, this is similar to how a non-subquery is reported, so the change is an improvement. sql/ha_partition.cc Comment change. sql/item_subselect.cc In Item_subselect::cleanup(), "new" engine is always deleted after an execution. Item_in_subselect::setup_engine() now builds the materialization engine for one execution only. Specific arena is no longer needed. In subselect_union_engine::cleanup(), call to unit->reinit_exec_mechanism is deleted because it is redundant. Implementation of cleanup() functions for subclasses of subselect_engine has been refactored. subselect_hash_sj_engine::setup() replaces init_permanent() and init_runtime(). For class subselect_hash_sj_engine, freeing of the temporary result table is moved from the destructor to ::cleanup(). Some changes necessary because of interface changes. sql/item_subselect.h Some cleanup of the subselect_engine classes: - All virtual functions now marked virtual in all derived classes. - Functions that returned bool result are now declared accordingly. - Slight cleanup of private/protected/public performed. - Improved constructors. sql/sql_lex.h Change in friend declaration. sql/sql_select.cc Function JOIN::reinit() is renamed to JOIN::reset(). JOIN::destroy() will now delete connection to associated tables. The st_select_lex_unit object is no longer cleaned up as early as before, but instead relying on the general cleanup of lex->unit in mysql_execute_command(). sql/sql_select.h A few small interface changes. sql/sql_union.cc Cleaned up some confusing error handling in st_select_lex_unit::exec() and st_select_lex::cleanup().
[3 May 2011 1:01]
Paul DuBois
Noted in 5.6.3 changelog. Table objects associated with one session's optimizer structures could be closed after being passed to another session, prematurely ending the second sesion's table or index scan. CHANGESET - http://lists.mysql.com/commits/132215
