CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `MY_VIEW` AS

select col1,col2,isnumeric(col3) = 0

FROM myTable

The isnumeric function referenced in the statement is a custom function and am including the definition and CREATE statement here.

CREATE DEFINER=`MY_USR’@’%` FUNCTION `isnumeric`(val varchar(1024)) RETURNS tinyint(1)
DETERMINISTIC
return val regexp ‘^(-|\+)?([0-9]+\.[0-9]*|[0-9]*\.[0-9]+|[0-9]+)$’

but getting this error message:

ERROR 1449 (HY000): The user specified as a definer (‘MY_USR’@’%’) does not exist

As added information – I’ve been able to execute CREATE TABLE statements & other other DDL statememts with the same logon – so privileges don’t appear to be an issue. I created all the objects and also executing the statements as root. How can  I fix this issue?

Answer: Based on the details presented – the CREATE FUNCTION is using the DEFINER=`MY_USR’@’%`.   A few things to mention about using the DEFINER . From the MariaDB documentation – “Each function has an account associated as the definer. By default, the definer is the account that created the function.”

Based on this definition because the FUNCTION CREATE uses the MY_USER@% user  and then the login user uses the xff@localhost user, causing MariaDB to think that the current user does not have permission to access the function.

If you rebuild the function without the DEFINER=`MY_USR’@’%`  part , and then rerun the statement – it should work.

Author: Jack Vamvas