Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
@Phil Thank you for remembering my signature !!! –  RolandoMySQLDBA Feb 5 '13 at 4:07
    
It had to be done :) –  FreshPhilOfSO Feb 5 '13 at 9:05
    
SHOW PROCEDURE STATUS WHERE name='sp_name' returns a row. So, how do I view the query it's executing if show create procedure sp_name says the the procedure doesn't exit and the routine_definition column is null in information_schema.routines –  ryy Feb 5 '13 at 15:11
    
Please paste the output of SHOW PROCEDURE STATUS WHERE name='sp_name' into your question. –  RolandoMySQLDBA Feb 5 '13 at 15:41
    
@RolandoMySQLDBA it says query executed successfully but it does not return any result. –  ryy Feb 5 '13 at 16:41
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.