Easy fix for ERROR 1449 (HY000): The user specified as a definer does not exist

08 April,2021 by Rambler

Question : I'm trying to execute a CREATE VIEW statement on a MariaDB 10.5 database. The top part of statement is included here:

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.

 

Read more on MariaDB commands & troubleshooting

MariaDB is number function

Quick access to MariaDB Cheatsheet

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Easy fix for ERROR 1449 (HY000): The user specified as a definer does not exist


dba-ninja.com