Bug #41110 crash with handler command when used concurrently with alter table
Submitted: 28 Nov 2008 17:37 Modified: 18 Mar 2009 14:50
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S1 (Critical)
Version:5.1.30 OS:Any
Assigned to: Davi Arnaut
Tags: handler
Triage: Triaged: D1 (Critical)

[28 Nov 2008 17:37] Shane Bester
Description:
running a mix of stress tests caused this crash:

mysqld.exe!get_lock_data()[lock.cc:828]
mysqld.exe!mysql_lock_tables()[lock.cc:214]
mysqld.exe!mysql_ha_read()[sql_handler.cc:480]
mysqld.exe!mysql_execute_command()[sql_parse.cc:3933]
mysqld.exe!mysql_parse()[sql_parse.cc:5791]
mysqld.exe!dispatch_command()[sql_parse.cc:1202]
mysqld.exe!do_command()[sql_parse.cc:857]
mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000024603A40=handler `qa05` read `idx0` last
thd->thread_id=114
thd->killed=NOT_KILLED

How to repeat:
very tricky. i'll try pinpoint the minimum statements needed.

gypsy --queryfile=qa_nasty.sql  --duration=3600 --skip-init=0 --shuffle-queries=1 --threads=10 --ignore-error=1064
[28 Nov 2008 21:12] Shane Bester
another possible stack trace:

ntdll.dll!RtlEnterCriticalSection()
mysqld.exe!thr_lock()[thr_lock.c:509]
mysqld.exe!thr_multi_lock()[thr_lock.c:974]
mysqld.exe!mysql_lock_tables()[lock.cc:279]
mysqld.exe!mysql_ha_read()[sql_handler.cc:480]
mysqld.exe!mysql_execute_command()[sql_parse.cc:3933]
mysqld.exe!mysql_parse()[sql_parse.cc:5791]
mysqld.exe!dispatch_command()[sql_parse.cc:1202]
mysqld.exe!do_command()[sql_parse.cc:857]
mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()

e invalid and cause the dump to abort...
000033C0060=handler `qa00` read `idx0` next
[28 Nov 2008 21:39] Shane Bester
debug binary asserts with Assertion failed: 0, file .\protocol.cc, line 416
081128 23:41:54 - mysqld got exception 0x80000003 ;
[28 Nov 2008 21:45] Shane Bester
testcase

Attachment: bug41110.c (text/plain), 7.85 KiB.

[4 Dec 2008 14:32] Shane Bester
when fixing this, please check bug #41112 which has the same testcase.
[5 Feb 2009 18:07] Davi Arnaut
Reduced test case:

create table t1 (a int);
insert into t1 values (1);
handler t1 open;
connect (alter,localhost,root,,);
send alter table t1 engine=memory;
connection default;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "rename result table" and info = "alter table t1 engine=memory";
--source include/wait_condition.inc
handler t1 read a next;

Problem happens when a table which has a associated SQL handler is being re-opened during a read and the new engine for the table does not support SQL handlers (ie: memory and innodb).
[9 Feb 2009 12:43] Davi Arnaut
Even with Bug#41110 and Bug#41112 fixed, test case deadlocks. Need to investigate it later.
[9 Feb 2009 13:20] 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/65605

2793 Davi Arnaut	2009-02-09
      Bug#41110: crash with handler command when used concurrently with alter table
      Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table
      
      The problem is that the server wasn't handling robustly failures
      to re-open a table during a HANDLER .. READ statement. If the
      table needed to be re-opened due to it's storage engine being
      altered to one that don't support HANDLER, a reference (dangling
      pointer) to a closed table could be left in place and accessed in
      later attempts to fetch from the table using the handler. Also,
      if the server failed to set a error message if the re-open
      failed. These problems could lead to server crashes or hangs.
      
      The solution is to remove any references to a closed table and
      to set a error if reopening a table during a HANDLER .. READ
      statement fails.
[24 Feb 2009 9:22] 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/67316

2760 Davi Arnaut	2009-02-24
      Bug#41110: crash with handler command when used concurrently with alter table
      Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table
      
      The problem is that the server wasn't handling robustly failures
      to re-open a table during a HANDLER .. READ statement. If the
      table needed to be re-opened due to it's storage engine being
      altered to one that doesn't support HANDLER, a reference (dangling
      pointer) to a closed table could be left in place and accessed in
      later attempts to fetch from the table using the handler. Also,
      if the server failed to set a error message if the re-open
      failed. These problems could lead to server crashes or hangs.
      
      The solution is to remove any references to a closed table and
      to set a error if reopening a table during a HANDLER .. READ
      statement fails.
      
      There is no test case in this change set as the test depends on
      a testing feature only available on 5.1 and later.
     @ sql/sql_handler.cc
        Remove redundant reopen check.
        Set errors even if reopening table.
        Reset TABLE_LIST::table reference when the table is closed.
[24 Feb 2009 9:56] 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/67329

2812 Davi Arnaut	2009-02-24 [merge]
      Bug#41110: crash with handler command when used concurrently with alter table
      Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table
      
      The problem is that the server wasn't handling robustly failures
      to re-open a table during a HANDLER .. READ statement. If the
      table needed to be re-opened due to it's storage engine being
      altered to one that doesn't support HANDLER, a reference (dangling
      pointer) to a closed table could be left in place and accessed in
      later attempts to fetch from the table using the handler. Also,
      if the server failed to set a error message if the re-open
      failed. These problems could lead to server crashes or hangs.
      
      The solution is to remove any references to a closed table and
      to set a error if reopening a table during a HANDLER .. READ
      statement fails.
     @ mysql-test/include/handler.inc
        Add test case for Bug#41110 and Bug#41112
     @ mysql-test/r/handler_innodb.result
        Add test case result for Bug#41110 and Bug#41112
     @ mysql-test/r/handler_myisam.result
        Add test case result for Bug#41110 and Bug#41112
     @ sql/sql_handler.cc
        Remove redundant reopen check.
        Set errors even if reopening table.
        Reset TABLE_LIST::table reference when the table is closed.
[24 Feb 2009 9:59] Davi Arnaut
Queued to 5.0-bugteam, 5.1-bugteam and 6.0-bugteam
[24 Feb 2009 10:25] 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/67334

3072 Davi Arnaut	2009-02-24 [merge]
      Merge Bug#41110 into 6.0-bugteam.
[9 Mar 2009 14:12] Bugs System
Pushed into 5.0.79 (revid:joro@sun.com-20090309135922-a0di9ebkxoj4d4wv) (version source revid:aelkin@mysql.com-20090224143545-7xc77386o8mg623c) (merge vers: 5.0.79) (pib:6)
[13 Mar 2009 1:41] Paul Dubois
Noted in 5.0.79 changelog.

The server could crash or hang if a table opened with HANDLER was 
altered to use a different storage engine that does not support
HANDLER. 

Setting report to NDI pending push into 5.1.x/6.0.x.
[13 Mar 2009 1:46] Paul Dubois
Revised changelog message:

The server did not robustly handle problems hang if a table opened
with HANDLER needed to be re-opened because it had been altered to
use a different storage engine that does not support HANDLER. The
server also failed to set an error if the re-open attempt failed.
These problems could cause the server to crash or hang.
[13 Mar 2009 19:05] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:patrick.crews@sun.com-20090225081629-ent6zn9d1lt6bx68) (merge vers: 5.1.33) (pib:6)
[14 Mar 2009 1:38] Paul Dubois
Noted in 5.1.33 changelog.

Setting report to NDI pending push into 6.0.x.
[18 Mar 2009 13:17] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:davi.arnaut@sun.com-20090224102254-36qv5h5k1j1tmfsw) (merge vers: 6.0.10-alpha) (pib:6)
[18 Mar 2009 14:50] Paul Dubois
Noted in 6.0.11 changelog.
[9 May 2009 16:40] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:37] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:35] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)