Database user specified as a definer

Question

I have a view in my database. problem is below

Error SQL query:

SELECT * 
FROM `lumiin_crm_prod`.`v_contact` 
LIMIT 1 ;

MySQL said:

1449 - The user specified as a definer ('lumicrm'@'%') does not exist

i Google for a solution

User is created for Host & not for Global.

How to create the User for Global ????

Answer 1

Long story short: You do not have the user 'lumicrm'@'%' on the DB Server.

Login to MySQL and run

SELECT user,host FROM mysql.user WHERE user='lumicrm';

Let's say you see the user 'lumicrm'@'localhost'

You have to create the user so that the view can be recognized. There are three(3) things you can try

Try This #1

Login to MySQL as lumicrm and run this command

SHOW GRANTS FOR CURRENT_USER();

Given the output of that query:

• Copy the output of that query to a Text File
• Change the host portion of the userhost to '%';
• Copy and Paste into a MySQL Session to create the View.

Try This #2

CREATE TABLE mysql.lumicrm LIKE mysql.user;
INSERT INTO mysql.lumicrm SELECT * FROM mysql.user WHERE user='lumicrm';
UPDATE mysql.lumicrm SET host='%';
INSERT INTO mysql.user SELECT * FROM mysql.lumicrm;
FLUSH PRIVILEGES;
DROP TABLE mysql.lumicrm;

Now you have the MySQL user 'lumicrm'@'localhost' hacked in

Try This #3

Dump the Views to a Text File. Here are my posts on how to do this:

• Jun 26, 2011 : Modify DEFINER on Many Views
• Jan 19, 2012 : Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
• May 02, 2012 : Problem with View When Restoring a MySQL Backup

Edit the user for the View with the appropriate username. Copy the View Creation Code and Paste into a MySQL Session to create the View.

Give it a Try !!!