Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A view, procedure or trigger names a DEFINER account that this server does not have

ErrorERROR 1449 (HY000): The user specified as a definer ('old_admin'@'localhost') does not exist

Short answer

Views, stored routines, triggers and events remember the account that created them. After a restore on another server that account may not exist, and the object fails with error 1449 when it is used. Either create that account with the right privileges, re-create the object under an account that exists, or remove the DEFINER clauses from the dump before loading it.

  • Applies to MySQL 8.4 LTS (syntax from the manual), MariaDB 10.11 (reproduced and fixed on a scratch server)
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    List every object in the restored database together with its definer, then compare that list with the accounts that exist. Any definer without a matching account (the pair user@host must match exactly) is the cause, and the list tells you how many objects need changing.

    SELECT 'view' AS kind, table_name AS name, definer FROM information_schema.views WHERE table_schema = 'app'
    UNION ALL SELECT routine_type, routine_name, definer FROM information_schema.routines WHERE routine_schema = 'app'
    UNION ALL SELECT 'trigger', trigger_name, definer FROM information_schema.triggers WHERE trigger_schema = 'app'
    UNION ALL SELECT 'event', event_name, definer FROM information_schema.events WHERE event_schema = 'app';
    
    SELECT user, host FROM mysql.user ORDER BY user, host;
  2. 2Match what you see, then jump to its fix

    1. 1The restore itself succeeded, but the first SELECT from a view, CALL of a procedure or INSERT that fires a trigger fails with 1449; the definer is a user from the old server.The account that created the objects does not exist on the new serverFix
    2. 2The old account should not exist on this server at all (a developer's name, a decommissioned admin), so recreating it would be wrong.You want the objects owned by an account that does existFix
    3. 3You are moving a database between environments with different accounts, and want everything owned by whoever runs the restore.The dump should not carry definers at all (restore again from a cleaned file)Fix
    4. 4The object that fails is a view, and nobody wants a named service account behind it.A view should run with the caller's rights instead of its creator'sFix

What does the error look like?

  • ERROR 1449 (HY000): The user specified as a definer ('old_admin'@'localhost') does not exist
  • SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('app'@'%') does not exist

Cause 1 of 4

The account that created the objects does not exist on the new server

1Check

A dump records the creator of each object as DEFINER. The manual says a routine, view or event whose definer account does not exist fails at the time it is used, with an error. A definer is a user name plus a host, so 'app'@'localhost' and 'app'@'%' are different accounts. Compare the two lists from the first step.

grep -o "DEFINER=\`[^\`]*\`@\`[^\`]*\`" dump.sql | sort | uniq -c

2Fix

Create the missing account under its exact name and host, with a password, and give it the privileges the objects use when they run. In definer context only the definer's privileges count: a procedure that updates a table needs UPDATE on it for the definer, and callers need EXECUTE on the procedure.

CREATE USER 'old_admin'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'old_admin'@'localhost';
GRANT EXECUTE ON app.* TO 'old_admin'@'localhost';

3Verify

Use the object that failed: the view, the procedure and an insert that fires the trigger.

SELECT COUNT(*) FROM app.v;
CALL app.p();

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

You want the objects owned by an account that does exist

1Check

Use the list from the first step. The manual allows changing a view's or event's definer in place with ALTER VIEW and ALTER EVENT, but stored procedures, functions and triggers must be dropped and created again with the new definer. Naming another account as definer needs the SET_ANY_DEFINER privilege; without it you may only name your own account.

SHOW CREATE VIEW app.v\G
SHOW CREATE PROCEDURE app.p\G
SHOW CREATE TRIGGER app.tr\G

2Fix

ALTER VIEW takes the full SELECT again, so copy it from SHOW CREATE VIEW. For routines and triggers, drop the object and run the CREATE statement again from the SHOW CREATE output, adding a DEFINER or leaving it out so it defaults to the creating account.

-- views: the SELECT is the one SHOW CREATE VIEW printed
ALTER DEFINER = CURRENT_USER VIEW app.v AS SELECT id, a FROM app.t;

-- events
ALTER DEFINER = CURRENT_USER EVENT app.nightly_cleanup;

-- routines and triggers: drop, then create again
DROP PROCEDURE app.p;
CREATE DEFINER = CURRENT_USER PROCEDURE app.p() SELECT COUNT(*) FROM app.t;

3Verify

No object lists the old account any more.

SELECT COUNT(*) FROM information_schema.views WHERE table_schema = 'app' AND definer LIKE 'old_admin%';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

The dump should not carry definers at all (restore again from a cleaned file)

1Check

When a definition has no DEFINER, the manual says the definer defaults to the user who creates it. In a mysqldump or mariadb-dump file the clause appears as DEFINER=`user`@`host` in views, routines, triggers and events, sometimes inside version comments. Count them before changing anything.

grep -c "DEFINER=" dump.sql

2Fix

Remove the clause from a copy of the dump, then load it into an empty database with the account that should own everything. The expression below deletes only the DEFINER=`user`@`host` text, so a view's SQL SECURITY DEFINER stays. Test it on a scratch database first, and keep the original dump.

sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' dump.sql > dump.nodefiner.sql
grep -c "DEFINER=" dump.nodefiner.sql        # expect 0
mysql -e "DROP DATABASE IF EXISTS app; CREATE DATABASE app"
mysql app < dump.nodefiner.sql

3Verify

Every object now lists the restoring account as its definer.

SELECT DISTINCT definer FROM information_schema.routines WHERE routine_schema = 'app';

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

A view should run with the caller's rights instead of its creator's

1Check

Views and routines have an SQL SECURITY setting, DEFINER (the default) or INVOKER. The manual says that with INVOKER the DEFINER attribute has no effect on execution, and the caller's own privileges are checked. Look at the current setting.

SELECT table_name, definer, security_type FROM information_schema.views WHERE table_schema = 'app';

2Fix

Re-create the view with SQL SECURITY INVOKER, then make sure each user of the view has privileges on the underlying tables, since those users' rights are now what count. This changes who may read the data, so decide it on purpose.

ALTER SQL SECURITY INVOKER VIEW app.v AS SELECT id, a FROM app.t;

3Verify

A user with SELECT on the base table can read the view, and a user without cannot.

SELECT * FROM app.v;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Create the same service accounts on every environment before restoring, so definers resolve.
  • Give objects a dedicated definer such as 'app_owner'@'localhost' from the start, not a developer's name.
  • Run a check after each restore that lists definers without a matching account.
  • Do not drop an account before moving its objects; the manual says DROP USER fails while an account is a definer, unless the executor holds the ALLOW_NONEXISTENT_DEFINER privilege.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.