MySQL Bugs: #58730: Assertion failed: table->key_read == 0 in close_thread

Bug #58730	Assertion failed: table->key_read == 0 in close_thread_table, temptable views
Submitted:	4 Dec 2010 13:26	Modified:	11 Jan 2011 16:30
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.5.7-debug, 5.5.9-debug	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	regression

Description:
similar to old closed bug 46791

Version: '5.5.7-rc-debug'  socket: ''  port: 3306  MySQL Community Server - Debug (GPL)
Assertion failed: table->key_read == 0, file ..\..\mysql-5.5.7-rc\sql\sql_base.cc, line 1526

mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.c:521]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:163]
mysqld-debug.exe!close_thread_table()[sql_base.cc:1526]
mysqld-debug.exe!close_open_tables()[sql_base.cc:1295]
mysqld-debug.exe!close_thread_tables()[sql_base.cc:1513]
mysqld-debug.exe!open_and_lock_tables()[sql_base.cc:5379]
mysqld-debug.exe!open_and_lock_tables()[sql_base.h:476]
mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:4428]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2065]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5499]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1029]
mysqld-debug.exe!do_command()[sql_parse.cc:769]
mysqld-debug.exe!do_handle_one_connection()[sql_connect.cc:745]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:684]
mysqld-debug.exe!pthread_start()[my_winthread.c:61]
mysqld-debug.exe!_callthreadstartex()[threadex.c:348]
mysqld-debug.exe!_threadstartex()[threadex.c:331]

5.1.53-debug was not affected.

How to repeat:
#on debug build:
drop table if exists `g1`,`g7`;
create table `g1` (`a1` int) engine=innodb;
insert into g1 values (1),(1);
create table `g7` (`b` int, key (`b`)) engine=innodb;

create or replace algorithm=temptable view `v` as 
select 1 from `g1`
left join `g1` `g2` on 1> (select 1 from `g1`)  ;
create or replace algorithm=temptable view `vz` as  

select 1 from `g7`;
explain select 1 from `g1`
left join `v` on 1 join `g7` on `g7`.`b`  <> 
(select 1 from `g1`,`vz`);

Verified with current mysql-5.5-security tree on Mac OS X:

macbook-pro:5.5-sec openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.9-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists `g1`,`g7`;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> create table `g1` (`a1` int) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into g1 values (1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create table `g7` (`b` int, key (`b`)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)

mysql> 
mysql> create or replace algorithm=temptable view `v` as 
    -> select 1 from `g1`
    -> left join `g1` `g2` on 1> (select 1 from `g1`)  ;
Query OK, 0 rows affected (0.10 sec)

mysql> create or replace algorithm=temptable view `vz` as  
    -> 
    -> select 1 from `g7`;
Query OK, 0 rows affected (0.10 sec)

mysql> explain select 1 from `g1`
    -> left join `v` on 1 join `g7` on `g7`.`b`  <> 
    -> (select 1 from `g1`,`vz`);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 
101204 15:32:30 mysqld_safe mysqld restarted
mysql> exit
Bye
macbook-pro:5.5-sec openxs$ tail -80 data/macbook-pro.err 
...
101204 11:58:15  InnoDB: 1.1.4 started; log sequence number 1595675
101204 11:58:15 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
101204 11:58:15 [Note] Event Scheduler: Loaded 0 events
101204 11:58:15 [Note] /Users/openxs/dbs/5.5-sec/bin/mysqld: ready for connections.
Version: '5.5.9-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (table->key_read == 0), function close_thread_table, file /Users/openxs/bzr2/mysql-5.5-security/sql/sql_base.cc, line 1513.
101204 15:32:30 - mysqld got signal 6 ;
...

In 5.1-bugteam, the last query of the testcase gets error:
"Subquery returns more than 1 row", which I don't understand, as the subquery actually has no row, not "more than one":
  select 1 from `g1`,`vz`
is an empty result, because vz is empty because g7 is empty.
So someone should check whether the 5.1-bugteam behaviour is really correct.
Anyway, the crash itself was introduced by:
 Konstantin Osipov	2010-07-27
      revision-id:kostja@sun.com-20100727102553-b4n2ojcyfj79l2x7
      A pre-requisite patch for the fix for Bug#52044.
      This patch also fixes Bug#55452 "SET PASSWORD is
      replicated twice in RBR mode".
(according to bzrfind and I verified this).
Note: I could not repeat the crash with MyISAM tables, had to use InnoDB.

Slightly simplified test case:

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (b INT, KEY (b)) engine=innodb;
INSERT INTO t1 VALUES (1),(1);

CREATE algorithm=temptable VIEW v1 AS
  SELECT 1 FROM t1 LEFT JOIN t1 t3 ON 1 > (SELECT 1 FROM t1);
CREATE algorithm=temptable VIEW v2 AS SELECT 1 FROM t2;

--error ER_SUBQUERY_NO_1_ROW
EXPLAIN SELECT 1 FROM t1 JOIN v1 ON 1 > (SELECT 1 FROM v2);

DROP TABLE t1, t2;
DROP VIEW v1, v2;

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/126511

3183 Jon Olav Hauglid	2010-12-10
      Bug #58730 Assertion failed: table->key_read == 0 in close_thread_table,
                 temptable views
      
      The TABLE::key_read field indicates if the optimizer has found that row
      retrieval only should access the index tree. The triggered assert
      inside close_thread_table() checks that this field has been reset when
      the table is about to be closed.
      
      During normal execution, these fields are reset right before tables are
      closed at the end of mysql_execute_command(). But in the case of errors,
      tables are closed earlier. The patch for Bug#52044 refactored the open
      tables code so that close_thread_tables() is called immediately if
      opening of tables fails. At this point in the execution, it could
      happend that all TABLE::key_read fields had not been properly reset,
      therefore triggering the assert.
      
      The problematic statement in this case was EXPLAIN where the query
      accessed two derived tables and where the first derived table was
      processed successfully while the second derived table was not.
      Since it was an EXPLAIN, TABLE::key_read fields were not reset after
      successful derived table processing since the state needs to be 
      accessible afterwards. When processing of the second derived table
      failed, it's corresponding SELECT_LEX_UNIT was cleaned, which caused
      it's TABLE::key_read fields to be reset. Since processing failed,
      the error path of open_and_lock_tables() was entered and
      close_thread_tables() was called. The assert was then triggered due
      to the TABLE::key_read fields set during processing of the first
      derived table.
      
      This patch fixes the problem by adding a new derived table processor,
      mysql_derived_cleanup() that is called when mysql_derived_filling()
      fails. It causes cleanup of all SELECT_LEX_UNITs to be called,
      resetting all relevant TABLE::key_read fields.
      
      Test case added to derived.test.

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/126533

3184 Jon Olav Hauglid	2010-12-10
      Bug #58730 Assertion failed: table->key_read == 0 in close_thread_table,
                 temptable views
      
      The TABLE::key_read field indicates if the optimizer has found that row
      retrieval only should access the index tree. The triggered assert
      inside close_thread_table() checks that this field has been reset when
      the table is about to be closed.
      
      During normal execution, these fields are reset right before tables are
      closed at the end of mysql_execute_command(). But in the case of errors,
      tables are closed earlier. The patch for Bug#52044 refactored the open
      tables code so that close_thread_tables() is called immediately if
      opening of tables fails. At this point in the execution, it could
      happend that all TABLE::key_read fields had not been properly reset,
      therefore triggering the assert.
      
      The problematic statement in this case was EXPLAIN where the query
      accessed two derived tables and where the first derived table was
      processed successfully while the second derived table was not.
      Since it was an EXPLAIN, TABLE::key_read fields were not reset after
      successful derived table processing since the state needs to be 
      accessible afterwards. When processing of the second derived table
      failed, it's corresponding SELECT_LEX_UNIT was cleaned, which caused
      it's TABLE::key_read fields to be reset. Since processing failed,
      the error path of open_and_lock_tables() was entered and
      close_thread_tables() was called. The assert was then triggered due
      to the TABLE::key_read fields set during processing of the first
      derived table.
      
      This patch fixes the problem by adding a new derived table processor,
      mysql_derived_cleanup() that is called after mysql_derived_filling().
      It causes cleanup of all SELECT_LEX_UNITs to be called, resetting
      all relevant TABLE::key_read fields.
      
      Test case added to derived.test.

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/127043

3202 Jon Olav Hauglid	2010-12-16
      Bug #58730 Assertion failed: table->key_read == 0 in close_thread_table,
                 temptable views
      
      The TABLE::key_read field indicates if the optimizer has found that row
      retrieval only should access the index tree. The triggered assert
      inside close_thread_table() checks that this field has been reset when
      the table is about to be closed.
      
      During normal execution, these fields are reset right before tables are
      closed at the end of mysql_execute_command(). But in the case of errors,
      tables are closed earlier. The patch for Bug#52044 refactored the open
      tables code so that close_thread_tables() is called immediately if
      opening of tables fails. At this point in the execution, it could
      happend that all TABLE::key_read fields had not been properly reset,
      therefore triggering the assert.
      
      The problematic statement in this case was EXPLAIN where the query
      accessed two derived tables and where the first derived table was
      processed successfully while the second derived table was not.
      Since it was an EXPLAIN, TABLE::key_read fields were not reset after
      successful derived table processing since the state needs to be 
      accessible afterwards. When processing of the second derived table
      failed, it's corresponding SELECT_LEX_UNIT was cleaned, which caused
      it's TABLE::key_read fields to be reset. Since processing failed,
      the error path of open_and_lock_tables() was entered and
      close_thread_tables() was called. The assert was then triggered due
      to the TABLE::key_read fields set during processing of the first
      derived table.
      
      This patch fixes the problem by adding a new derived table processor,
      mysql_derived_cleanup() that is called after mysql_derived_filling().
      It causes cleanup of all SELECT_LEX_UNITs to be called, resetting
      all relevant TABLE::key_read fields.
      
      Test case added to derived.test.

Pushed to mysql-5.5-bugteam and merged to mysql-trunk-bugfixing.

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/127064

3201 Jon Olav Hauglid	2010-12-16
      Bug #58730 Assertion failed: table->key_read == 0 in close_thread_table,
                 temptable views
      
      The TABLE::key_read field indicates if the optimizer has found that row
      retrieval only should access the index tree. The triggered assert
      inside close_thread_table() checks that this field has been reset when
      the table is about to be closed.
      
      During normal execution, these fields are reset right before tables are
      closed at the end of mysql_execute_command(). But in the case of errors,
      tables are closed earlier. The patch for Bug#52044 refactored the open
      tables code so that close_thread_tables() is called immediately if
      opening of tables fails. At this point in the execution, it could
      happend that all TABLE::key_read fields had not been properly reset,
      therefore triggering the assert.
      
      The problematic statement in this case was EXPLAIN where the query
      accessed two derived tables and where the first derived table was
      processed successfully while the second derived table was not.
      Since it was an EXPLAIN, TABLE::key_read fields were not reset after
      successful derived table processing since the state needs to be 
      accessible afterwards. When processing of the second derived table
      failed, it's corresponding SELECT_LEX_UNIT was cleaned, which caused
      it's TABLE::key_read fields to be reset. Since processing failed,
      the error path of open_and_lock_tables() was entered and
      close_thread_tables() was called. The assert was then triggered due
      to the TABLE::key_read fields set during processing of the first
      derived table.
      
      This patch fixes the problem by adding a new derived table processor,
      mysql_derived_cleanup() that is called after mysql_derived_filling().
      It causes cleanup of all SELECT_LEX_UNITs to be called, resetting
      all relevant TABLE::key_read fields.
      
      Test case added to derived.test.

Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:jon.hauglid@oracle.com-20101216095523-uguxqyzv0aai9wkb) (merge vers: 5.5.8) (pib:24)

Pushed into mysql-trunk 5.6.1 (revid:georgi.kodinov@oracle.com-20101217125013-y8pb3az32rtbplc9) (version source revid:jon.hauglid@oracle.com-20101216100953-8cjpw04foh2kqy3g) (merge vers: 5.6.1) (pib:24)

Noted in 5.5.9 changelog.

EXPLAIN could crash for queries that accessed two derived tables.