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
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)
mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p
After lots of attempt，the solution is delete user mysql.infoschema first and then do vamshi's steps:
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)
DROP USER 'mysql.infoschema'@'localhost';
The rest of the solution is like previous answers.
mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;
I transfered my users table from another mysql server to a new installation and ran into this error. So, if you don't have the mysql.infoschema user, you can try:
sudo systemctl stop mysql
sudo mysqld --upgrade=FORCE
This fixed for me.