Update MySQL Table Causes No Specified Definer Error

Question

I have a table with basic employee information (i.e. name, department and title). While I have no problems adding or deleting from this table, updating is giving me an odd error.

This is happening both on the test environment and the live environment that I work on. The error is "The user specified as a definer ('remote_admin'@'%') does not exist". I'm unsure what this error means or why this is only occurring on table update.

I'm updating the table simply by using the SQL:

Update office_employee 
Set Title='Title Here' 
Where id=117;

The code I'm using in my test environment is this:

<? 
    $db = new DbConnect(Config::dbhost, Config::dbuser, Config::dbpassword, "intranet", Config::error_reporting, Config::persistent);

    if($db->open()) {
        echo '<p>Successfully Connected</p>';

        $sql = "Select * FROM office_employees";

        $sql2 = "Update office_employees Set Title='Title Here' where id=117";

        echo $sql2;
        echo '<br />';      
        echo $sql;

        $db->query($sql2) or die($db->error());

        $results = mysql_query($sql);
        echo '<br />';
        echo $results;

            while($row = mysql_fetch_array($results)) {
                echo '<br />';
                print_r($row);
                echo '<br />';
            }//*/

        $db->close();

    } else {
        echo '<p>Unsuccessful in Connecting</p>';
    }
?>

EDIT

The Database only has one user, the admin. There's no reason to add another user. Also, this used to work fine (according to co-workers, I'm relatively new) so it wouldn't make sense as to why this would suddenly need the definer changed. There has been no movement of the database, either, to cause any odd errors.

Possible duplicate of [MySQL error 1449: The user specified as a definer does not exist](http://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist) — aynber, Jan 20 '17 at 16:33

score 3 · Accepted Answer · answered Jan 20 '17 at 16:48

The error is occurring because there is a BEFORE UPDATE and/or AFTER UPDATE trigger defined on the table. The trigger definition includes the DEFINER; essentially, the user that created the trigger.

Based on the error message, we know that the trigger is defined with DEFINER 'remote_admin'@'%'.

When the UPDATE statement is executed, the TRIGGER is being fired. And the error is thrown because the trigger definition references a user that doesn't exist.

Based on the error message, we know that the user 'remote_admin'@'%' is not defined in the mysql.user table.

Most likely explanation for this is that the user has been removed.

That's what the error means, and why it's occurring when the UPDATE statement is executed.

Thank you, I listed all the Triggers for the database and the update trigger is the only one listed and I was able to find the definer. — ebbBliss, Jan 20 '17 at 16:56

Update MySQL Table Causes No Specified Definer Error

1 Answers1