0

I have setup my site in a way that I develop on localhost and then just updates it and all is good to go then but, there is a problem with the Stored Procedures.

When I create a stored procedure on localhost and then using sqlyog copy that to live server then its copied normally but, am not able to alter it on live. To do so I have to create another one with some dummy name and then manually copy & paste the code of SP on live and execute to create it.

Any idea what might be the issue here?

UPDATE

I just found its issue with definer I think because executing the SP results in this error:

The user specified as a definer ('root'@'localhost') does not exist

I wan't expecting this problem because I removed the definer before copying the procedure but, its their. seems like

sqlyog adds it by force maybe

. Any idea to fix this?

Junaid
  • 2,084
  • 1
  • 20
  • 30

1 Answers1

0

when you go live then in place of

('root'@'localhost')

Put username in place of root and host address in place of localhost.It may works for you with same.

Ajay Kadyan
  • 1,081
  • 2
  • 13
  • 36
  • I wish I could Alter the SP on live, that's my problem am unable to alter – Junaid Jul 12 '12 at 08:04
  • is user have permission to do so?? – Ajay Kadyan Jul 12 '12 at 08:07
  • I remove the `definer` from localhost and then copy it to live, but mysql is adding the definer on its own, and on live I am not using `root` that is the issue. – Junaid Jul 12 '12 at 08:13
  • if you are not using root then only creator can alter that SP.according to my knowledge. – Ajay Kadyan Jul 12 '12 at 08:17
  • Ajay bro give me one good answer please, I usually add the ` drop if not exist` statement in the SP on production too, is it going to recreate the SP every time its called? like the call StoredProc(some args) will drop first and then create the SP? on each and every call? – Junaid Jul 12 '12 at 08:25
  • on every call of SP its not create it again.Creation is done once only. – Ajay Kadyan Jul 12 '12 at 08:47