The user specified as a definer ('root'@'%') does not exist

Question

I have a trigger defined as follow:

USE `veeva_bi`;
DELIMITER $$
DROP TRIGGER IF EXISTS veeva_bi.account_ai$$
USE `veeva_bi`$$
CREATE DEFINER = CURRENT_USER TRIGGER `veeva_bi`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    // do trigger stuff here
END$$
DELIMITER ;

When I run that query I got the trigger created as root@localhost:

CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_bi`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    // do trigger stuff here
END

But any time I try to run a PHP script, that connects to MariaDB as root user I got this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('root'@'%') does not exist' in /var/www/html/veeva_replicator/DB.php:158

Why is that? I have others triggers created on the same way and them did works but this one doesn't, any advice or help regarding this?

Answer 1

I met this problem when update a row in mysql.

I connect mysql use a diffrent user and password rather than root.

When I type "show triggers" in mysql, I found the triger use "root@%", this will cause this exception.

Answer 2

Has to be a PROXY situation. As MySQL's documentation says:

When authentication to the MySQL server occurs by means of an authentication plugin, the plugin may request that the connecting (external) user be treated as a different user for privilege-checking purposes. This enables the external user to be a proxy for the second user; that is, to have the privileges of the second user. In other words, the external user is a “proxy user” (a user who can impersonate or become known as another user) and the second user is a “proxied user” (a user whose identity can be taken on by a proxy user).

Run:

 GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

You can refer to @RolandoMySQLDBA's answer.