MySQL Bugs: #65762: function does not exist

Bug #65762	function does not exist
Submitted:	28 Jun 2012 13:27	Modified:	29 Jun 2012 7:16
Reporter:	sachin jain	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	function does not exist

Description:
The following function (by importing .sql file in sqlyog) executes successfully on my machine but gives syntax error on client's machine on the same version of mysql (5.0.18). 

Error Code: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FUNCTION `traverse_tree`(inpUsrId int unsigned) RETURNS varchar(1000) CHARSET la' at line 1

The function code is given below:

DELIMITER $$

DROP FUNCTION IF EXISTS `mydb`.`traverse_tree_comma`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `traverse_tree_comma`(inpUsrId int unsigned) RETURNS varchar(1000) CHARSET latin1
    DETERMINISTIC
BEGIN
declare UsrStr VARCHAR(1000) DEFAULT NULL;
declare vUsr VARCHAR(20);
declare vSuprUsr VARCHAR(20);
declare vRepTo VARCHAR(20);
declare Cntr BOOLEAN DEFAULT TRUE ;
declare vRole VARCHAR(20);
declare vUsrFNm VARCHAR(30);
declare vUsrLNm VARCHAR(30);
declare crUsrId int;
DECLARE v_done BOOLEAN DEFAULT FALSE; 
declare Cur_USRLST CURSOR for
select TUSRM.N_USER_ID SuprId,TROLM.N_LEVEL_ID RoleNm,TUSRM.S_FIRST_NAME firstname,
TUSRM.S_LAST_NAME lastname,TUSRM.N_REPORTING_TO_ID repid FROM T_USER TUSRM ,T_ROLE_MASTER TROLM
WHERE TUSRM.N_ROLE_ID = TROLM.N_ROLE_ID 
AND TUSRM.N_USER_ID = IFNULL((select N_REPORTING_TO_ID from t_user where N_USER_ID = crUsrId),
TUSRM.N_USER_ID);
DECLARE CONTINUE HANDLER FOR NOT FOUND         
SET v_done := TRUE; 
set crUsrId = inpUsrId;
ab : LOOP
OPEN Cur_USRLST;
FETCH Cur_USRLST INTO vSuprUsr,vRole,vUsrFNm,vUsrLNm,vRepTo;
CLOSE Cur_USRLST;
IF v_done THEN 
SET v_done := FALSE;  
RETURN '0';  
END IF;                       
IF vRepTo =1 THEN
IF Cntr THEN
set UsrStr = vSuprUsr;
ELSE
set UsrStr = Concat(UsrStr ,',',vSuprUsr);
END IF;
leave ab;
ELSEIF Cntr THEN
set UsrStr = vSuprUsr;
set Cntr = FALSE;
ELSE
set UsrStr = Concat(UsrStr ,',',vSuprUsr);
END IF;
set crUsrId = vSuprUsr;
END LOOP;
RETURN UsrStr;
END$$

DELIMITER ;

When I copy it in query window and executes after removing charset and deterministic clause it executes successfully & gives:

(0 row(s) affected)
(0 ms taken)

SQL Yog shows the created function in Object Browser but when I right click and alter function, it says function does not exist.

How to repeat:
Still this could not be repeated on another machine

I think you should better report this to SQLYog's support. 

If we speak about MySQL server bug here, please, make sure the same problem happens when you use mysql command line client on recent version of MySQL server, 5.0.91+ at least.

@Sachin.

You report that SQLyog does/does not this or that.  The MySQL supporters here do not care about SQLyog or any other client from a 3rd party vendor!  You should see in SQLyog HISTROY tab what is the last executed SQL-query is returning this error. The server returns the error on a SQL-statement.  The client interface does not matter.  The statement whne opening ALTER PROCEDURE from SQLyog Object Browser is simply:

SHOW CREATE PROCEDURE `database_name`.`routine_name`;   

When you are experiencing problems when using SQLyog you are welcome to report to us here: http://www.webyog.com/forums. But the short answer (here and in our Forums) to what you are reporting is that you should not use MySQL 5.0.18 at all. This is an older server with several known bugs in relation to Stored Routines (they also were quite new in MySQL at the time 5.0.18 was released). You should at least upgrade the server to 5.0.96.  Better to a recent  5.1x or 5.5x (but avoid 5.5.25 due to this: http://bugs.mysql.com/bug.php?id=65745).

Peter 
(not a MySQL/Oracle person
but from Webyog - the vendor behind SQLyog)

I have also checked the same using mysql commandline but no succes. It shows OK...
0 rows affected. But functions are not created

This is what I get in mysql command line client when recent MySQL version is used:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter $$
mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `traverse_tree_comma`(inpUsrId
 int unsigned) RETURNS varchar(1000) CHARSET latin1
    ->     DETERMINISTIC
    -> BEGIN
    -> declare UsrStr VARCHAR(1000) DEFAULT NULL;
    -> declare vUsr VARCHAR(20);
    -> declare vSuprUsr VARCHAR(20);
    -> declare vRepTo VARCHAR(20);
    -> declare Cntr BOOLEAN DEFAULT TRUE ;
    -> declare vRole VARCHAR(20);
    -> declare vUsrFNm VARCHAR(30);
    -> declare vUsrLNm VARCHAR(30);
    -> declare crUsrId int;
    -> DECLARE v_done BOOLEAN DEFAULT FALSE;
    -> declare Cur_USRLST CURSOR for
    -> select TUSRM.N_USER_ID SuprId,TROLM.N_LEVEL_ID RoleNm,TUSRM.S_FIRST_NAME
firstname,
    -> TUSRM.S_LAST_NAME lastname,TUSRM.N_REPORTING_TO_ID repid FROM T_USER TUSR
M ,T_ROLE_MASTER TROLM
    -> WHERE TUSRM.N_ROLE_ID = TROLM.N_ROLE_ID
    -> AND TUSRM.N_USER_ID = IFNULL((select N_REPORTING_TO_ID from t_user where
N_USER_ID = crUsrId),
    -> TUSRM.N_USER_ID);
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND
    -> SET v_done := TRUE;
    -> set crUsrId = inpUsrId;
    -> ab : LOOP
    -> OPEN Cur_USRLST;
    -> FETCH Cur_USRLST INTO vSuprUsr,vRole,vUsrFNm,vUsrLNm,vRepTo;
    -> CLOSE Cur_USRLST;
    -> IF v_done THEN
    -> SET v_done := FALSE;
    -> RETURN '0';
    -> END IF;
    -> IF vRepTo =1 THEN
    -> IF Cntr THEN
    -> set UsrStr = vSuprUsr;
    -> ELSE
    -> set UsrStr = Concat(UsrStr ,',',vSuprUsr);
    -> END IF;
    -> leave ab;
    -> ELSEIF Cntr THEN
    -> set UsrStr = vSuprUsr;
    -> set Cntr = FALSE;
    -> ELSE
    -> set UsrStr = Concat(UsrStr ,',',vSuprUsr);
    -> END IF;
    -> set crUsrId = vSuprUsr;
    -> END LOOP;
    -> RETURN UsrStr;
    -> END$$
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> DELIMITER ;
mysql> show create function traverse_tree_comma\G
*************************** 1. row ***************************
            Function: traverse_tree_comma
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `traverse_tree_
comma`(inpUsrId int unsigned) RETURNS varchar(1000) CHARSET latin1
    DETERMINISTIC
BEGIN
declare UsrStr VARCHAR(1000) DEFAULT NULL;
declare vUsr VARCHAR(20);
declare vSuprUsr VARCHAR(20);
declare vRepTo VARCHAR(20);
declare Cntr BOOLEAN DEFAULT TRUE ;
declare vRole VARCHAR(20);
declare vUsrFNm VARCHAR(30);
declare vUsrLNm VARCHAR(30);
declare crUsrId int;
DECLARE v_done BOOLEAN DEFAULT FALSE;
declare Cur_USRLST CURSOR for
select TUSRM.N_USER_ID SuprId,TROLM.N_LEVEL_ID RoleNm,TUSRM.S_FIRST_NAME firstna
me,
TUSRM.S_LAST_NAME lastname,TUSRM.N_REPORTING_TO_ID repid FROM T_USER TUSRM ,T_RO
LE_MASTER TROLM
WHERE TUSRM.N_ROLE_ID = TROLM.N_ROLE_ID
AND TUSRM.N_USER_ID = IFNULL((select N_REPORTING_TO_ID from t_user where N_USER_
ID = crUsrId),
TUSRM.N_USER_ID);
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_done := TRUE;
set crUsrId = inpUsrId;
ab : LOOP
OPEN Cur_USRLST;
FETCH Cur_USRLST INTO vSuprUsr,vRole,vUsrFNm,vUsrLNm,vRepTo;
CLOSE Cur_USRLST;
IF v_done THEN
SET v_done := FALSE;
RETURN '0';
END IF;
IF vRepTo =1 THEN
IF Cntr THEN
set UsrStr = vSuprUsr;
ELSE
set UsrStr = Concat(UsrStr ,',',vSuprUsr);
END IF;
leave ab;
ELSEIF Cntr THEN
set UsrStr = vSuprUsr;
set Cntr = FALSE;
ELSE
set UsrStr = Concat(UsrStr ,',',vSuprUsr);
END IF;
set crUsrId = vSuprUsr;
END LOOP;
RETURN UsrStr;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.03 sec)

No problem, as far as I can see. So, please, upgrade to some recent and supported version, better 5.1+, and then copy/paste the output from mysql command line client (as I did above) if you still see any problem while creating this function.

Thanks a lot to all. I got it. It was due to SQL Yog bug which was showing function does not exist. I run show create statement it shows the function. 

But one thing is not clear how these functions are corrupted automatically. It was the second time when I had to create them on client's server.

Anyways thanks once again for your valuable reply.

This problem was not a result of any bug in (recent) MySQL code.