Solved

MySQL Create Procedure - Access Denied

Posted on 2008-10-13
3
880 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 142

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
First name pregmatch 11 39
problems with mysql ODBC Connector and vbscript 4 31
sql_mode 1 24
unable to insert record into a table 2 29
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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