MySQL Bugs: #34895: 'show procedure status' or 'show function status' + 'flush tables' crashes

Bug #34895	'show procedure status' or 'show function status' + 'flush tables' crashes
Submitted:	27 Feb 2008 16:49	Modified:	18 Dec 2009 20:31
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.86	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	flush tables, show function status, show procedure status

Description:
server crashes when 'show procedure status' and 'flush tables' are run concurrently

mysqld-debug.exe!Item_field::used_tables
mysqld-debug.exe!setup_fields
mysqld-debug.exe!JOIN::prepare
mysqld-debug.exe!mysql_select
mysqld-debug.exe!handle_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart

probably related to bug #19589 - opening this new bug with testcase + info.

How to repeat:
#create some procedures

delimiter ;
drop procedure if exists `p0`;
drop procedure if exists `p1`;
drop procedure if exists `p2`;
drop procedure if exists `p3`;
drop procedure if exists `p4`;
create procedure `p0`() begin end;
create procedure `p1`() begin end;
create procedure `p2`() begin end;
create procedure `p3`() begin end;
create procedure `p4`() begin end;

drop procedure if exists `p5`;
delimiter //
create procedure `p5`(`num` int)
begin
 declare `i` int default 0;
 repeat
  show procedure status;
  flush tables;
  set `i`=`i`+1;
 until `i`>`num`
 end repeat;

end//

delimiter ;

#now in 2 or more threads run this:

mysql -uroot -e "call p5(1000000)" test > /dev/nul

Suggested fix:
fix locking

5.1.24-BK crashed with 2 threads on linux

Attachment: bug34895_5.1.24_stack_linux.txt (text/plain), 5.78 KiB.

'show function status' suffers the same fate. here's a testcase for that too:

#create some procedures

delimiter ;
drop function if exists `f0`;
drop function if exists `f1`;
drop function if exists `f2`;
drop function if exists `f3`;
drop function if exists `f4`;
create function `f0`() returns int return 1;
create function `f1`() returns int return 1;
create function `f2`() returns int return 1;
create function `f3`() returns int return 1;
create function `f4`() returns int return 1;

drop procedure if exists `f5`;
delimiter //
create procedure `f5`(`num` int)
begin
 declare `i` int default 0;
 repeat
  show function status;
  flush tables;
  set `i`=`i`+1;
 until `i`>`num`
 end repeat;

end//

delimiter ;

now "call f5(1000000)" in multiple threads

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

ChangeSet@1.2591, 2008-04-08 22:55:31+04:00, kostja@dipika.(none) +2 -0
  A fix for Bug#34895 "'show procedure status' or 'show function status' + 
  'flush tables' crashes"
  
  Don't add mysql.proc to the list of query tables to open when
  executing SHOW PROCEDURE STATUS/SHOW FUNCTION STATUS. It will be opened
  by I_S mechanisms anyway.
  
  No test case since the bug is repeatable in a concurrent environment as well.
  QQ: couldn't a similar double-open test case be constructed with 
  sub-statements?
  QQ: Is there a way perhaps to add a test case?

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

ChangeSet@1.2591, 2008-04-08 23:23:33+04:00, kostja@dipika.(none) +2 -0
  A fix for Bug#34895 "'show procedure status' or 'show function status' + 
  'flush tables' crashes"
  
  Don't add mysql.proc to the list of query tables to open when
  executing SHOW PROCEDURE STATUS/SHOW FUNCTION STATUS. It will be opened
  by I_S mechanisms anyway.
  
  No test case since the bug is repeatable in a concurrent environment as well.
  QQ: couldn't a similar double-open test case be constructed with 
  sub-statements?
  QQ: Is there a way perhaps to add a test case?

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

ChangeSet@1.2605, 2008-04-10 17:02:14+02:00, thek@adventure.(none) +2 -0
  Bug#34895 'show procedure status' or 'show function status' + 'flush tables' crashes
  
  The server crashes when 'show procedure status' and 'flush tables' are
  run concurrently.
  
  This is caused by the way mysql.proc table is added twice to the list
  of table to lock although the requirements on the current locking API
  assumes differently.
  
  No test case is submitted because of the nature of the crash which is 
  currently difficult to reproduce in a deterministic way.

Pushed into 6.0.6-alpha

Noted in 6.0.6 changelog.

Concurrent execution of FLUSH TABLES along with SHOW FUNCTION STATUS
or SHOW PROCEDURE STATUS could cause a server crash.

I confirm the flush tables crashes 5.1.37-debug:

Version: '5.1.37-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
mysqld: sql_class.cc:437: void Diagnostics_area::set_ok_status(THD*, ha_rows, ulonglong, const char*): Assertion `! is_set()' failed.
090627 12:48:34 - mysqld got signal 6 ;

So there is still a problem that this bugfix didn't solve correctly.

Shane: If the server now crashes in the Diagnostic area, it can very well be because of unrelated changes to 5.1, and not because the original bug had deep profound implications to a specific subsystem. Significant refactoring and bug patching is done on 5.1 all the time. In fact if we have an assertion in the Diagnostic area it would be better to open a new bug to better analyse this particular problem.

Also note that code was pushed into 5.1 which addresses the original problematic area, whether or not this bug patch was formally pushed into 5.1. It might very well be that the bug was solved and that it re-emerged again in a similar shape but because of totally different reasons.

Tried with 5.1.39-debug-log (from mysql-5.1-bugteam) and I can't repeat the problem using either procedures nor functions.  
Is this still an issue for a later release?

(running six threads concurrently calling p5 as described above.)

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

2812 Kristofer Pettersson	2009-09-30
      Bug#34895 'show procedure status' or 'show function status' +
                'flush tables' crashes
      
      The server crashes when 'show procedure status' and 'flush tables' are
      run concurrently.
      
      This is caused by the way mysql.proc table is added twice to the list
      of table to lock although the requirements on the current locking API
      assumes differently.
      
      No test case is submitted because of the nature of the crash which is 
      currently difficult to reproduce in a deterministic way.
      
      This is a backport from 5.1
     @ myisam/mi_dbug.c
        * check_table_is_closed is only used in EXTRA_DEBUG mode but since it is
        iterating over myisam shared data it still needs to be protected by an
        appropriate mutex.
     @ sql/sql_yacc.yy
        * Since the I_S mechanism is already handling the open and close of 
        mysql.proc there is no need for the method sp_add_to_query_tables.

Pushed into 5.0.87 (revid:joro@sun.com-20091006073202-rj21ggvo2gw032ks) (version source revid:kristofer.pettersson@sun.com-20090930125025-hqndeob36ef5sxqp) (merge vers: 5.0.87) (pib:11)

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20091002112748-2xmjv846dk323nc3) (merge vers: 5.1.40) (pib:11)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.0.87, 5.1.40, 5.5.0 changelogs.

For 6.0.x, already fixed in 6.0.6.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)