I cant see SQL Queries in MySQL Stored Procedure!

views:

134

answers:

I cant see SQL Queries in MySQL Stored Procedure!

I have created numbers of stored procedures in MySQL through remote access. And those procedures worked well.

After a few days when I connected to Database through remote access. All stored procedures were alive but while was trying to call those procedures, I realized that SQL queries inside procedures had gone. Anyone knows the reason?

+1 A:

If SHOW CREATE PROCEDURE (see docs) isn't showing you the procedures (with SQL queries inside) you probably have some permission problems -- you need to be the owner of the procedure or have SELECT access to the mysql.proc table.

Alex Martelli 2009-07-05 15:56:06

system allows you to add procedure once and you can call it. but the next time when you connected, it doesnt allow you to call the procedure that you created before. thats really odd.

HamdiKavak 2009-07-05 16:06:38

it should be something else.

HamdiKavak 2009-07-05 16:07:10

so check your grants regarding mysql.proc SELECT access, and who owns those stored procedures -- "being really odd" never stopped mysql from doing things a certain way, the permission and ownership data are indispensable on your way to understanding the problem.

Alex Martelli 2009-07-05 16:49:17

another possibility is that you needed a COMMIT in the previous session (to avoid having the procedure just disappear in an implicit rollback) -- I think that's not normally the case (autocommit on create being the norm), but maybe your mysql installation/configuration is peculiar. That would be my next best guess if the procedure just doesn't show up despite you having all needed permissions!-)

Alex Martelli 2009-07-05 17:27:12

I get message SELECT command denied to user '****'@'localhost' for table 'proc'. I guess the problem is using shared hosting and its limitations :) I have to get my own server soon. Thanx for reply

HamdiKavak 2009-07-05 17:54:16

So you can't SELECT in mysql.proc, but don't give up yet -- you might yet be allowed to use SHOW PROCEDURE STATUS or look at INFORMATION_SCHEMA (the ROUTINES table there). Yeah, shared hosting has limitations, your own server gives you far more power, but that doesn't yet mean you're totally hosed (some shared hosting providers are better than others...!)...

Alex Martelli 2009-07-05 19:46:41

ansaurus

tags:

views:

answers:

I cant see SQL Queries in MySQL Stored Procedure!

related questions