[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2862
  • Last Modified:

MySQL error 1449 "There is no 'User'@'IP' registered" when executing a stored procedure

I had a stored procedure working on one MySQL Server.  I scripted out the DB and imported it to another server.

Now when I execute the procedure i get the error in the title.

I checked my script, and there is no "DEFINER" clause in my procedure.  

I dropped the procedure & added it again.

no better.

The Drop / Create for the procedure are attached. (using $$ as delimiter)

What am I doing wrong?

Thanks!
DROP PROCEDURE uspUserLogin $$

CREATE PROCEDURE uspUserLogin(
p_UserName varchar( 20 ) ,
PwdHash varchar( 255 )
) BEGIN SELECT users.ID, users.UserName, userTypes.UserTypeName, users.FirstName, users.LastName, users.Extension
FROM `wt_collectors` AS users
INNER JOIN `wt_usertyes` AS userTypes ON userTypes.UserTypeID = users.UserTypeID
WHERE UserName = p_UserName
AND PASSWORD = PwdHash
AND Active =1;

END $$

Open in new window

0
Daniel Wilson
Asked:
Daniel Wilson
  • 3
1 Solution
 
jazzIIIloveCommented:
Hi there;

Go to MySQL administrator, select schemata, views, select a view, edit SQL view SQL and look for the part that reads "DEFINER=`[username]`@`localhost`" and change the '[username]' reference to root, or another user that exists on the new server as appropriate

taken: http://forums.mysql.com/read.php?11,140528,165613#msg-165613

Best regards.
0
 
Daniel WilsonAuthor Commented:
Thanks, but I already found that article.  As I said,

I checked my script, and there is no "DEFINER" clause in my procedure.  

I dropped the procedure & added it again.

no better.

Other ideas?
0
 
Daniel WilsonAuthor Commented:
Hold on, you're at least partly right.

the procedure queries a view ... and its Definer is set wrong.
0
 
Daniel WilsonAuthor Commented:
I re-ran the
Create Or Replace VIEW
script & all is well.

Thanks!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now