16

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

tcetin
  • 979
  • 1
  • 21
  • 48
  • Does this answer your question? [MySQL error 1449: The user specified as a definer does not exist](https://stackoverflow.com/questions/10169960/mysql-error-1449-the-user-specified-as-a-definer-does-not-exist) – Ersoy Jun 01 '20 at 08:11
  • Not. It gives same error when I run the ALTER statement. – tcetin Jun 01 '20 at 08:26

5 Answers5

35
  1. Delete the problematic user
DROP USER 'mysql.infoschema'@'localhost';

The rest of the solution is like previous answers.

  1. Create the user again
  2. Grant it permissions
mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password';

mysql> GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;
Marah
  • 505
  • 5
  • 10
  • Worked for me too! Grant select is refused with error "ERROR 1410 (42000): You are not allowed to create a user with GRANT mysql" in case I attempt it on the existing problematic user. But if following this answer, delete it, create new one, grant permissions - all works. – Siniša Jun 09 '21 at 17:21
  • What password do I set it to? – User Sep 01 '21 at 15:09
  • Hey @User You can use any password within the single quotes, a string password of your own related to this user. – om-ha Oct 13 '21 at 16:40
  • I have tried tons of solutions only this worked. Thanks. – X999 Jun 09 '22 at 14:17
12

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.

gir
  • 121
  • 1
  • 2
6

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)
Vamshi
  • 116
  • 3
  • 1
    I tried. Getting like this:mysql> show grants for 'mysql.infoschema'@'localhost'; ERROR 1141 (42000): There is no such grant defined for user 'mysql.infoschema' on host 'localhost' mysql> CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password'; ERROR 1396 (HY000): Operation CREATE USER failed for 'mysql.infoschema'@'localhost' mysql> GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`; ERROR 1410 (42000): You are not allowed to create a user with GRANT mysql> – tcetin Jun 01 '20 at 09:31
  • What is the user you logged in as.. ? probably you do not have permissions. I would login as root that has every permission and I replicated your issue to ensure the solution I suggested works fine. you can find the user you are logged in as using `select user()` and grants using `show grants` You usually get that error if you are trying to using GRANT to create a user and grant permissions at the same time since Mysql 8. Try separate commands, fist to create user and then to add grants. – Vamshi Jun 01 '20 at 09:36
  • Interesting @kodcu, can you show the output of the command `mysql> show grants\G;` – Vamshi Jun 01 '20 at 09:52
  • mysqld --upgrade=FORCE helps – dragonfly Dec 09 '21 at 21:39
4

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)
Jan Sršeň
  • 1,045
  • 3
  • 23
  • 46
xiaoer wang
  • 41
  • 1
0
mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p

source https://laracasts.com/discuss/channels/laravel/error-1449-hy000-the-user-specified-as-a-definer-mysqlinfoschema-at-localhost-does-not-exist?signup

dromi
  • 1