MySQL Bugs: #45781: infinite hang/crash in "opening tables" after handler tries to open merge table

Bug #45781	infinite hang/crash in "opening tables" after handler tries to open merge table
Submitted:	26 Jun 2009 9:28	Modified:	7 Mar 2010 12:42
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	5.1.35, 5.1.37	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	handler, merge, regression

Description:
Infinite hang and 100% cpu usage after handler tries to open merge table.
5.0.82 never had this problem, so it's a regression introduced in 5.1.

If you execute 'mysqladmin shutdown' during this hang, debug server asserts:

mysqld: table.cc:407: void free_table_share(TABLE_SHARE*): Assertion `share->ref_count == 0' failed.
090610 14:54:04 - mysqld got signal 6 ;

mysqld.exe!_NMSG_WRITE()[crt0msg.c:198]
mysqld.exe!abort()[abort.c:59]
mysqld.exe!_wassert()[assert.c:212]
mysqld.exe!free_table_share()[table.cc:407]
mysqld.exe!table_def_free_entry()[sql_base.cc:271]
mysqld.exe!my_hash_free_elements()[hash.c:93]
mysqld.exe!my_hash_free()[hash.c:114]
mysqld.exe!table_def_free()[sql_base.cc:296]
mysqld.exe!clean_up()[mysqld.cc:1254]
mysqld.exe!unireg_end()[mysqld.cc:1192]
mysqld.exe!kill_server()[mysqld.cc:1142]
mysqld.exe!handle_shutdown()[mysqld.cc:2946]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]

How to repeat:
#might want to adjust 'nice' for mysqld before running this!

flush tables;
drop table if exists m1,t1;
create table t1(a int)engine=myisam;
create table m1(a int)engine=merge union=(t1);
select 1 from m1;
handler m1 open;
drop table if exists m1,t1;
select 1 from m1; #hangs on 5.1

Test causes:
mysqltest: At line 6: query 'handler m1 open' failed: 1031: Table storage engine for 'm1' doesn't have this option

Could this bug possibly have been fixed by patch for Bug#41110 ?

..possibly not since I'm using version 5.1.37-debug

that error occurs, but then the next statement hangs as described.

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

3000 Kristofer Pettersson	2009-07-09
      Bug#45781 infinite hang/crash in "opening tables" after handler tries to open merge
      table
      
      The MERGE table storage engine does not support the HA_CAN_SQL_HANDLE feature
      and any attempt to open the merge table will fail with ER_ILLEGAL_HA.
      
      After an error occurred the tables that was opened must be closed again
      or they will be left in an inconsistent state. However, the assumption
      made in the code for closing and register handler tables was that only
      one table will be opened, and this is not true for MERGE tables which
      will cause multiple tables to open.
      
      The next time a SELECT operation was issued on the merge table it
      caused the system to freeze.
      
      This patch fixes this issue by making sure that all tables which
      are opened also are closed in the event of an error.
     @ mysql-test/r/merge.result
        * Added test case for bug 45781
     @ mysql-test/t/merge.test
        * Added test case for bug 45781
     @ sql/sql_handler.cc
        * open_tables can consist of more than one element; find the last element before
          attempting to merge the list with handler_tables
        * Apply close_thread_table to all items in handler_tables in case of an error.

Please find comments on IRC (copied to private email).

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

3017 Konstantin Osipov	2009-07-15
      A fix for  Bug#45781 "infinite hang/crash in "opening tables" after handler tries
      to open merge table".
      
      Or, put differently, a code review for Kristoffer's patch.
      
      Discussed the problem with Dmitri and the solution implemented
      in this patch seems to be least nasty of all.

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

3024 Kristofer Pettersson	2009-07-16
      Bug#45781 infinite hang/crash in "opening tables" after handler tries to open merge
                table
      
      The MERGE table storage engine does not support the HA_CAN_SQL_HANDLE feature
      and any attempt to open the merge table will fail with ER_ILLEGAL_HA.
      
      After an error occurred the tables that was opened must be closed again
      or they will be left in an inconsistent state. However, the assumption
      made in the code for closing and register handler tables was that only
      one table will be opened, and this is not true for MERGE tables which
      will cause multiple tables to open.
      
      The next time a SELECT operation was issued on the merge table it
      caused the system to freeze.
      
      This patch fixes this issue by making sure that all tables which
      are opened also are closed in the event of an error.
     @ mysql-test/r/merge.result
        Added test case for bug 45781
     @ mysql-test/t/merge.test
        Added test case for bug 45781
     @ sql/sql_handler.cc
        * mysql_ha_open() was never ment to open more than one table. If we encounter more tables, we should
          close all tables related to the current substatement and raise an exception.

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

3470 Kristofer Pettersson	2009-07-16
      bug#45781 infinite hang/crash in "opening tables" after handler tries to open merge
                table
      
      Fixed Merge mistake between 5.1->pe

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:dao-gang.qu@sun.com-20090717025250-n2lq5mqibjfdpigu) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090923103200-kyo2bakdo6tfb2fb) (merge vers: 6.0.14-alpha) (pib:11)

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

An entry has been added to each of the following changelogs: 6.0.14, 5.4.4, 5.1.38

An infinite hang and 100% CPU usage occurred after handler tried to open a merge table.

If the command mysqladmin shutdown was executed during the hang, the debug server generated the following assert:

mysqld: table.cc:407: void free_table_share(TABLE_SHARE*): Assertion `share->ref_count ==
0' failed.

090610 14:54:04 - mysqld got signal 6 ;

Noted in 5.5.0 changelog.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091210084103-l4f8u62u4evoy3dc) (pib:16)

Setting report to Need Merge pending push of Celosia into release tree.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

Already fixed in earlier 5.5.x.