Issue
I am trying to list all tables from mysql database on ubuntu os. But I am getting this error all time;
mysql> use mysql;
Database changed
mysql> show tables;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
I have checked my mysql version:
mysql Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)
So it seems it is last version of mysql.
How can I fix this error?
Please help
Solution
This error occurs when there is any view / trigger in that database (mysql in your case) that has a definer (in other words a user) that is a definer for the view but then the user itself doesnt exists. A fresh install of mysql should have that user
mysql> select user,host from mysql.user where user='mysql.infoschema';
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
+------------------+-----------+
1 row in set (0.00 sec)
With the permissions :
mysql> show grants for 'mysql.infoschema'@'localhost';
+-------------------------------------------------------+
| Grants for mysql.infoschema@localhost |
+-------------------------------------------------------+
| GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost` |
+-------------------------------------------------------+
1 row in set (0.00 sec)
For some reason that user is missing / deleted in your users list and is why when you list tables its also checking for views in it and complaining about its missing definer.
Solution :
Simply create the user with the permissions above and that should stop showing the error.
Create user:
mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
Grant permissions:
mysql> GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Answered By - Vamshi