[SOLVED] ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

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