Bug #32828 Database Crash when type in "Show Procedure Status"
Submitted: 28 Nov 2007 21:46 Modified: 24 Dec 2008 7:32
Reporter: Clement Ho Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.1.22 Beta OS:Windows (2003)
Assigned to: CPU Architecture:Any
Tags: crash, PROCEDURE, show status

[28 Nov 2007 21:46] Clement Ho
Description:
When I type in SQL command "Show Procedure Status" in a mysql client command line. The Database engine crashed. and I have to manually restart it.
I have a MS Visual 2005 installed so I have a traces stack attach to this report.
-----------
mysqld.exe!get_field(st_mem_root * mem=0x06569990, Field * field=0x00000000, String * res=0x09a7eb80)  Line 2526 + 0x7 bytes	C++
 	mysqld.exe!store_schema_proc(THD * thd=0x06568260, st_table * table=0x09294d48, st_table * proc_table=0x092a2768, const char * wild=0x00000000, int full_access=1, const char * sp_user=0x09a7ec58)  Line 3970	C++
 	mysqld.exe!fill_schema_proc(THD * thd=0x06568260, TABLE_LIST * tables=0x09292720, Item * cond=0x00000000)  Line 4041 + 0x17 bytes	C++
 	mysqld.exe!get_schema_tables_result(JOIN * join=0x092a0788, enum_schema_table_state executed_place=PROCESSED_BY_JOIN_EXEC)  Line 5834 + 0xc bytes	C++
 	mysqld.exe!JOIN::exec()  Line 1629 + 0x1c bytes	C++
 	mysqld.exe!mysql_select(THD * thd=0x06568260, Item * * * rref_pointer_array=0x06569440, TABLE_LIST * tables=0x09292720, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x00000000, unsigned int og_num=0, st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned __int64 select_options=2684635648, select_result * result=0x09293a40, st_select_lex_unit * unit=0x06569078, st_select_lex * select_lex=0x06569330)  Line 2273	C++
 	mysqld.exe!handle_select(THD * thd=0x06568260, st_lex * lex=0x06569018, select_result * result=0x09293a40, unsigned long setup_tables_done_option=0)  Line 258 + 0x79 bytes	C++
 	mysqld.exe!execute_sqlcom_select(THD * thd=0x00000000, TABLE_LIST * all_tables=0x09292720)  Line 4539 + 0xa bytes	C++
 	mysqld.exe!mysql_execute_command(THD * thd=0x06568260)  Line 1836 + 0xc bytes	C++
 	mysqld.exe!mysql_parse(THD * thd=0x06568260, const char * inBuf=0x09291d18, unsigned int length=21, const char * * found_semicolon=0x09a7fad0)  Line 5447	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x06568260, char * packet=0x09289ce9, unsigned int packet_length=22)  Line 955	C++
 	mysqld.exe!do_command(THD * thd=0x00000016)  Line 712 + 0xe bytes	C++
 	mysqld.exe!handle_one_connection(void * arg=0x06568260)  Line 1099 + 0xa bytes	C++
 	mysqld.exe!pthread_start(void * param=0x0658e050)  Line 62 + 0x3 bytes	C
>	mysqld.exe!_threadstart(void * ptd=0x0658dcc8)  Line 196 + 0x6 bytes	C
 	kernel32.dll!77e6608b() 	
 	[Frames below may be incorrect and/or missing, no symbols loaded for kernel32.dll]	

How to repeat:
Just type in "show procedure status". It will crashed.
[29 Nov 2007 0:04] MySQL Verification Team
Thank you for the bug report. I can't repeat the crash just issuing the
show procedure status command:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> delimiter $$
mysql> create procedure sp()
    -> begin
    -> select 1+1;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> show procedure status\G
*************************** 1. row ***************************
                  Db: test
                Name: sp
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2007-11-28 22:00:52
             Created: 2007-11-28 22:00:52
       Security_type: DEFINER
             Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.06 sec)

mysql>

Could you please provide the script to create the procedures you
have and also your my.ini file. Thanks in advance.
[6 Dec 2007 22:03] Clement Ho
I deleted the Database and recreate the database and tables. Now the "Show procedure status" is fine now however, when I try to create the procedure, it gave me error message.
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -u root -p -D storydb < c:\midnight_job.sp
"ERROR 1307 (HY000) at line 4: Failed to CREATE PROCEDURE Midnight_Job"
The following is my midnight_job.sp (It ran without a hich for version 5.0).
--------------------------------------------
delimiter //
drop procedure if exists Midnight_Job//
Create procedure Midnight_Job()
begin
declare mytime datetime;
# backing up the current table
Insert into tbl_split_proc_stat (Type,Time) values('Backup Start',Now());
 select Date_Format(Date_sub(Now(),Interval 1 day),'%Y-%m-%d') into mytime;
 Insert ignore into tbl_mainnews_bak select * from tbl_mainnews where storytime > mytime;
Insert into tbl_split_proc_stat (Type,Time) values('Backup End',Now());
# Copy data from current table to old table (> 1 mth)
Insert into tbl_split_proc_stat (Type,Time) values('Copy Start',Now());
 Select Date_Format(Date_sub(Now(),Interval 1 month),'%Y-%m-%d') into mytime;
 Insert ignore into tbl_mainnews_old select * from tbl_mainnews where storytime <= mytime;
Insert into tbl_split_proc_stat (Type,Time) values('Copy End',Now());
#Delete data from current table (> 1 mth)
Insert into tbl_split_proc_stat (Type,Time) values('Delete Start',Now());
 delete from tbl_mainnews where storytime < mytime;
Insert into tbl_split_proc_stat (Type,Time) values('Delete End',Now());
#Optimize Table
Insert into tbl_split_proc_stat (Type,Time) values('Opt Start',Now());
 Optimize table tbl_mainnews;
Insert into tbl_split_proc_stat (Type,Time) values('Opt End',Now());
end //
delimiter ;
---------------------------------------
[9 Dec 2007 10:15] Sveta Smirnova
Thank you for the feedback.

I can not repeat described behavior too.

Have you run mysql_upgrade?
[10 Dec 2007 21:18] Clement Ho
Thanks so much, after I ran mysql_upgrade command. it doesn't have anymore problem. :)
Thank you so much, Sveta Smirnova.

Clement
[24 Dec 2008 7:31] MySQL Verification Team
this is a duplicate of bug #41726