Solved

MySQL Create Procedure - Access Denied

Posted on 2008-10-13
3
883 Views
Last Modified: 2012-05-05
I've come across a few similar questions here, but have yet to figure out the answer.

I have a MySQL database that I connect to remotely (using Navicat) as the root user.

I'm trying to create a procedure using the following code (sent to me by a developer);

CREATE DEFINER=`root`@`localhost` PROCEDURE `webservice`.`Qry_WatchList`(_ProductId INT, _WebServiceId INT)
BEGIN
select WatchListId, WSProductId, Title, Description, Currency, Price
from tblwatchlist wl where wl.`ProductId`=_ProductId
and wl.`WebServiceId`=_WebServiceId ;
END $$

I'm getting 'access denied' messages for user root@ - and for life of me I can't figure out why.  I'm able to create databases, tables etc without any issues at all - but this simply will not execute at all.

I also tried;

CREATE PROCEDURE Qry_WatchList(_ProductId INT, _WebServiceId INT)
select WatchListId, WSProductId, Title, Description, Currency, Price
from tblwatchlist wl where wl.ProductId=_ProductId and wl.WebServiceId=_WebServiceId;

With the same result.

Any pointers very much appreciated!
0
Comment
Question by:Maxafi
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22709152
the DEFINER is without the host name..
CREATE DEFINER=`root` PROCEDURE `webservice`.`Qry_WatchList`(_ProductId INT, _WebServiceId INT)
BEGIN
 ...

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question