MySQL error 1449 occurs when a stored procedure, view, or event has been defined with a user as the definer, but that user does not exist. When such an object is executed, MySQL tries to execute it using the definer’s privileges. If the definer does not exist, then MySQL cannot execute the object and throws error 1449. To fix this error, the definer of the object should be changed to a valid user. This post will provide you with 3 possible solutions for the error:
MySQL error 1449: The user specified as a definer does not exist.
MySQL error 1449, commonly referred to as the ‘Definer Does Not Exist’ error, can be a challenging situation for DBAs and developers. This error arises when a stored procedure, view, or event references a non-existent user as the definer. In this article, we’ll delve into the root causes of this error and provide actionable solutions to rectify it effectively.
I wrote about DEFINER & INVOKER SQL SECURITY in MySQL long back in early 2012 which covers the explanation of how they work with respect to the stored routines in MySQL!
Here I’ll try to extend it little more with examples for the error and provide 3 solutions.
Causes of the ‘Definer Does Not Exist’ Error
The ‘Definer Does Not Exist’ error can emerge due to various scenarios:
- Definer Deletion: If the user specified as the definer is removed from the MySQL system, the error arises.
- Incomplete Migration: During database migration, if users are not fully transferred, the definer mismatch occurs.
- Security Changes: Altering user privileges without considering dependent objects can trigger this error.
Creating procedure for test
We will create a simple procedure to return count from table ‘a’ of database ‘test’ and a specific user as a DEFINER.
mysql> grant all on test.* to 'spuser'@'localhost' identified by 'sppass'; Query OK, 0 rows affected (0.04 sec)mysql> flush privileges; Query OK, 0 rows affected (0.04 sec)### Doc: If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately. mysql> DROP PROCEDURE IF EXISTS myproc; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DELIMITER $ mysql> CREATE DEFINER='spuser'@'localhost' PROCEDURE myproc() -> BEGIN -> select count(*) from test.a; -> END $ Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> call test.myproc(); +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Introducing the definer error
Alright the procedure call above worked fine as expected. Now let’s create a trouble! Let’s drop a user and try to see what do we get here.
mysql> drop user 'spuser'@'localhost'; Query OK, 0 rows affected (0.00 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)mysql> call test.myproc(); ERROR 1449 (HY000): The user specified as a definer ('spuser'@'localhost') does not exist
Hmmm… This is the error I wanted to point & explain. I encourage you to refer the DEFINER & INVOKER in SQL SECURITY explained in my previous article and ofcourse MySQL documentation is a bible.
Well so as the error says it is expecting a user which is not present. So the easy way out here is to create the dropped user, reload privileges and make a call.
Solution 1 – Create the missing user
mysql> grant all privileges on test.* to 'spuser'@'localhost' identified by 'sppass'; Query OK, 0 rows affected (0.01 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)mysql> call test.myproc(); +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Though we might not want to get into troubles at first! So how can we avoid from getting in to this situation?
The answer is using “SQL SECURITY” defined as “INVOKER”. SQL SECURITY here defines that the procedure will run under the INVOKER’s privileges. (Default is DEFINER)
Specifying INVOKER we are free from the dependency of DEFINER user.
Let’s test as follows:
– Create procedure with SQL SECURITY specified as INVOKER.
– Drop definer user
– call the procedure and…
Solution 2 – Use SQL SECURITY INVOKER
mysql> DROP PROCEDURE IF EXISTS myproc;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER $
mysql> CREATE DEFINER='spuser'@'localhost' PROCEDURE myproc()
-> SQL SECURITY INVOKER
-> BEGIN
-> select count(*) from test.a;
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> drop user 'spuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> call test.myproc();
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Hmmm so this look good! No error & stored procedure works well…even if we lost the user who created the procedure!
But there is an obvious understanding that the SQL SECURITY INVOKER clause may behave differently depending on privileges of the user who calls it.
Alright, finally I’ll add one more way to resolve the error: “MySQL error 1449: The user specified as a definer does not exist”
The stored procedure or say MySQL routines are stored in mysql.proc table which also reflects in information_schema.ROUTINES table.
One can directly update the mysql.proc table’s DEFINER column to replace deleted user with existing user. Let’s do that.
Solution 3 – change DEFINER user
mysql> DROP PROCEDURE IF EXISTS myproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER $
mysql> CREATE DEFINER='spuser'@'localhost' PROCEDURE myproc()
-> BEGIN
-> select count(*) from test.a;
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> select * from mysql.proc where name='myproc';
+------+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+------------------------------------------+------------------+---------------------+---------------------+----------+---------+----------------------+----------------------+-------------------+------------------------------------------+
| db | name | type | specific_name | language | sql_data_access | is_deterministic | security_type | param_list | returns | body | definer | created | modified | sql_mode | comment | character_set_client | collation_connection | db_collation | body_utf8 |
+------+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+------------------------------------------+------------------+---------------------+---------------------+----------+---------+----------------------+----------------------+-------------------+------------------------------------------+
| test | myproc | PROCEDURE | myproc | SQL | CONTAINS_SQL | NO | DEFINER | | | BEGIN
select count(*) from test.a;
END | spuser@localhost | 2015-03-20 23:57:53 | 2015-03-20 23:57:53 | | | utf8 | utf8_general_ci | latin1_swedish_ci | BEGIN
select count(*) from test.a;
END |
+------+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+------------------------------------------+------------------+---------------------+---------------------+----------+---------+----------------------+----------------------+-------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> drop user 'spuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> call test.myproc();
ERROR 1449 (HY000): The user specified as a definer ('spuser'@'localhost') does not exist
mysql> update mysql.proc set definer='root@localhost' where name='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from routines where routine_name='myproc';
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED | LAST_ALTERED | SQL_MODE | ROUTINE_COMMENT | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
| myproc | def | test | myproc | PROCEDURE | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SQL | BEGIN
select count(*) from test.a;
END | NULL | NULL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2015-03-20 23:58:51 | 2015-03-20 23:57:53 | | | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------+-----------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)
mysql> exit
Bye
root@ubuntu:~# mysql -uroot -pkedar
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> call test.myproc();
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Conclusion
The error “Definer does not exist” can be resolved by atleast 3 ways:
– Create the DEFINER (user).
– Change the DEFINER by updating mysql.proc.
– Bring in the INVOKER (with caution).
Well as such we updated DEFINER column in mysql.proc table to avoid user creation.
Do you see a possibility of 4th solution to update security_type column of mysql.proc to INVOKER? See if that works! 🙂
Let me know.
References
1. http://dev.mysql.com/doc/refman/5.0/en/stored-programs-security.html
2. http://kedar.nitty-witty.com/access-control-in-mysql-stored-routines-by-example-definer-invoker-sql-security
Update:
Why “flush tables” commands are “strikethrough-ed” –> because they’re not required.