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: | |
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
[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