2

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?

  • Do you have any user called 'root'@'%'? In your question you have 'root'@'localhost', they're from diferent host % and localhost – oNare Jul 20 '15 at 16:40
  • @oNare I don't even know why trigger is called using 'root'@'%' and not fom 'root'@'localhost' as they should be called – ReynierPM Jul 20 '15 at 16:44
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.

  • @rolandomysqldba says is better to delete that tables from MySQL and remove those privileges rather than run what you say, which one to follow? – ReynierPM Jul 20 '15 at 23:12
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.