SQL Error  [HY000]: (conn=38) The user specified as a definer (‘user1’@’%’) does not exist on MySQL / MariaDB
In MySQL, As per the error, it seems that the user does not exist when we tried to execute the function its definer role is defined with the user which is already dropped or deleted.
select sf_escapechars_test ('abc'); Error: SQL Error  [HY000]: (conn=38) The user specified as a definer ('user1'@'%') does not exist on MySQL / MariaDB Procudure definition: CREATE DEFINER=`user1`@`%` FUNCTION `classicmodels`.`sf_escapechars_test`
Cause: Definer defined for the procedure or function user is not present. “USER1” is already dropped why the function is giving an error while executing.
Solution: We have to modify all the procedure or function which has a definer role with the deleted user.
UPDATE `mysql`.`proc` p SET definer = 'root@%' WHERE definer='user1@%'
Now Execute the procedure which is using USER1 as the definer:
Now you have to open a new session and try to execute the procedure. It will work fine.
2nd solution: Recreate the drop user and assign its privileges:
GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Pingback: User drop its objects also dropped or exist in MySQL / MariaDB | Smart way of Technology