MySQL – Resolve Master Slave Error: User Specified as a Definer Does Not Exist

MySQLreplication

I setup mysql (version 5.5) master slave replication and when test it with root user from phpmyadmin data succesfully transferd. When start application slave have an error:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.1.1.31
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 6963420
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529310
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1449
                   Last_Error: Error 'The user specified as a definer ('dbadmin'@'%') does not exist' on query. Default database: 'mydb'. Query: 'update mytbl 
                                        set ....limit 1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6300951
              Relay_Log_Space: 1191935
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1449
               Last_SQL_Error: Error 'The user specified as a definer ('dbadmin'@'%') does not exist' on query. Default database: 'mydb'. Query: 'update mytbl 
                                        set ....limit 1'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

How to solve it ? maybe just add user 'dbadmin'@'%'?

Best Answer

Perhaps the GRANTs on the Slave are not consistent with those on the Master?

On Master, do SHOW GRANTS FOR 'dbadmin'@'%';. Then apply the grant(s) it gives you to the Slave.

Then do START SLAVE;

Related Solutions

Mysql – How to fix the definer problem The user specified as a definer (‘fred’@’192.168.0.%’) does not exist

If you will recreate all your views this way (and will create all new ones with this syntax):

CREATE
SQL SECURITY INVOKER
    VIEW applications
AS     
SELECT id, product_id
FROM modules     WHERE item_type='application';

you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and SELECT on tables involved.

Typical database user in a web application will have SELECT, INSERT, UPDATE, DELETE, EXECUTE until your web application creates database objects.

You do not need 'fred' to be a database user anywhere in this case even though this database user created this view on your web site.

Mysql – Database user specified as a definer

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

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

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.