MySQL Bugs: #31300: Failed to load routine

Bug #31300	Failed to load routine - after server restart
Submitted:	30 Sep 2007 11:11	Modified:	22 Dec 2007 22:08
Reporter:	Lobejko Tomasz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Procedure created as user for any schema is accesible only for time to first restart of MySQL server.
After server restarting procedure is declared and visible in table mysql.proc but client connected by network can not execute this procedure's.

How to repeat:
Create any procedure.
Try to execute this procedure.
Its work
Stop the server
Start the server
Tre run this procedure : its fails with info:
"Faile to load routine <proceudre name> The table mysql.proc is missing, corrupt or contains bad data (internal code -6)

Suggested fix:
I have find how to fix this:

You must execute two selects:

SELECT * FROM mysql.proc
show create procedure <procedure name>

After this two selects executing procedure <procedure name> is now working.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45, and inform about the results.

In version 5.0.45 the problem is too.
The problem dont exist if you try calling this procedures from self machine.
Problem exist only for users connected by network ???.
On one machine with version 5.0.45 user myuser@127.0.0.1 can call any procedures after server restart, but user localhost@127.0.0.1 can not.
After execute two selects (i earlie wrote about this) user localhost@127.0.0.1 can execute all proceures too.
Acess for either users are identical.

myuser@127.0.0.1 can call any procedures after
server restart, but user localhost@127.0.0.1

May be

myuser@127.0.0.1 can call any procedures after
server restart, but user myuser@localhost

of course.

Please, send exact and complete CREATE PROCEDURE statement you had used. Send the results of:

select user();

from the same session where CREATE PROCEDURE was executed.

Problem is not in procedure
Problem is in acess to MysqlServer.
On local machine in version 5.0.45 problem is not exist
On remote machine in version 5.0.45 problem is still as i wrote.

---- procedure

use fogo;
DELIMITER $$
DROP PROCEDURE IF EXISTS `fogo`.`p_katalogi_tree`$$
CREATE DEFINER=fogo@localhost PROCEDURE  `fogo`.`p_katalogi_tree` ()
SQL SECURITY INVOKER
begin
declare v_level int unsigned default 0;
declare v_position int unsigned default 1;
declare v_row_count int unsigned default 0;

-- table for storing the result
-- create table if not exists
-- tmp_katalogi_list(
-- pk int unsigned not null auto_increment -- insertion order
-- , id int unsigned -- id of the record
-- , level int unsigned -- level of search (depth)
-- , primary key(pk)
-- );

-- discard contents from prior calls

truncate table `fogo`.`tmp_katalogi_list`;

-- insert our entrty record
insert into `fogo`.`tmp_katalogi_list` (id, level)
select al.katalog_id, v_level
from `fogo`.`katalogi` al
where al.parent_id=-1
order by katalog_id
;

-- initialize our loop control variable
set v_row_count := (select count(*)
from `fogo`.`katalogi` al
where al.parent_id=-1
);

-- iterate, breadth first
while v_row_count != 0 DO

-- iterate, breadth first

-- insert all children for the current level
insert into `fogo`.`tmp_katalogi_list` ( id, level)
select al.katalog_id, v_level + 1
from `fogo`.`katalogi` al
inner join `fogo`.`tmp_katalogi_list` tal on al.parent_id = tal.id
where tal.level = v_level
order by id
;

-- update loop control.
-- if there weren't any children of this level,
-- no new rows were inserted, and the loop will terminate
set v_row_count := (select count(*) from `fogo`.`katalogi` al
inner join `fogo`.`tmp_katalogi_list` tal on al.parent_id = tal.id
where tal.level = v_level)
;

-- update the level
set v_level := v_level + 1;
end while;

-- show the result
-- select * from tmp_katalogi_list;
-- select 1 as OK;
end$$

DELIMITER ;
-----------------------
----Table tmp_katalogi_list
-----------------------
CREATE TABLE  `fogo`.`tmp_katalogi_list` (
  `pk` int(10) unsigned NOT NULL auto_increment,
  `id` int(10) unsigned default NULL,
  `level` int(10) unsigned default NULL,
  PRIMARY KEY  (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin2

select user();
---------------
mysql@localhost

i connected by ssh tunell to server 
ssh -C -L 13306:127.0.0.1:3306 ???.???.???.???

my program and mysadministrator connected to 127.0.0.1:13306 to the MySQL server on remote machine 
server show that i am mysq@localhost or fogo@localhost 
but i connected to mysql 127.0.0.1 13306 
or fogo 127.0.0.1 13306

Remote server i upgraded on Your request. I dont remember if i created procedure after server upgrade. For access to procedure I still using two select's as i wrote early.

Best  Regards
If you wont i can send to you backup of full database.

Thank you for the feedback.

> Problem is not in procedure
> Problem is in acess to MysqlServer.

Have you run mysql_upgrade script on remote MySQL server? If didn't, please, do it now. Also please try to repeat the bug again as "create procedure; use it; restart server; try to use procedure" and if problem still exists provide details about remote server operating system and hardware send us mysql.proc table.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".