How do I check if a stored procedure is defined in MySQL

Question

When I do :

select routine_definition
from information_schema.routines
where routine_name = 'sp_name';

it returns a row, but the routine_definition column is NULL. Does this mean that this Stored Procedure is not defined?

Also, when I do show create procedure sp_name, it says that procedure does not exit.

Answer 1

You should run these other queries

SHOW PROCEDURE STATUS WHERE name='sp_name';
SHOW FUNCTION STATUS WHERE name='sp_name';
SELECT type,db,body FROM mysql.proc WHERE name='sp_name'\G

If the procedure does not show up from any of these queries, my condolences.

There is one hope left: If you have a physical backup of the mysql.proc, do this in a Staging DB:

• service mysql stop
• Copy the backup of /var/lib/mysql into the Staging DB
• chown -R mysql:mysql /var/lib/mysql
• service mysql start

Once you do that, you can mysqldump all the stored procedures out of it with

MYSQLDUMP_OPTIONS="-uroot -p..."
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --all-databases"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-data"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --no-create-info"
mysqldump ${MYSQLDUMP_OPTIONS} > MySQLStoredProcs.sql

Now, go vi or nano the file and get the Stored Procedure out of it.

Give it a Try !!!

UPDATE 2013-02-05 11:58 EDT

If you are really having problems with visually seeing the Stored Procedure's code, you may have a corrupt proc.MYD file. Try retrieving from backup as I already mentioned.