Listing MySQL functions and Procedures

Maintaining and updating stored procedures and functions in MySQL often will require root access which in practice means that you might need to have some interaction with your hosting provider if you wish to use them.

Operating the the standard phpMyAdmin interface gives no support for them other then the ability to create them through script.

The following bits of SQL are useful for viewing stored procedures and functions in a hosted database accessed through phpMyAdmin

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="FUNCTION"
AND ROUTINE_SCHEMA="database"

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE="PROCEDURE"
AND ROUTINE_SCHEMA="database"

Also the "SHOW PROCEDURE STATUS" will give details of stored procedures and functions

Dealing with MySQL Function

Newer Versions of MySQL have the binary log which will generate te following error

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

To fix this you need incude the following keywords

• CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data.

• NO SQL indicates that the routine contains no SQL statements.

• READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).

Ther referenced artical indicates using
SET GLOBAL log_bin_trust_function_creators = 1;

References
http://mvnrepository.blogspot.com/2009/08/this-function-has-none-of-deterministic.html


Example
DELIMITER $$

DROP FUNCTION IF EXISTS `adadmin_adem`.`fDeleteTheme` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fDeleteTheme`(id_in INTEGER) RETURNS int(11)
DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA
BEGIN


DECLARE counter INTEGER;
DECLARE parent_id INTEGER;


SELECT count(id_theme) FROM s_themes where parent = id_in INTO counter;


IF counter = 0 THEN

SELECT parent FROM s_themes where id_theme = id_in INTO parent_id;
DELETE FROM adem.s_themes WHERE id_theme = id_in;
END IF;

RETURN parent_id;

END $$

DELIMITER ;