Home

Share via:

Mysql Error: The user specified as a definer mysql.infoschema...

Found an interesting MySQL error caused by changing privileges for the root user account.

193 views

Edited: 2020-03-27 08:54

I recently encountered an interesting MySQL Error after accidentally changing or corrupting the root user. The exact error goes a bit like this:

Mysql Error: The user specified as a definer ('mysql.infoschema'@'localhost') does not exist' when trying to dump tablespaces

This error is not accurate however, as the "infoschema" user did exist when I checked. Something must have gone bad by me changing privileges for the root user—but what exactly?

The error would occur even when I entered simple commands like SHOW DATABASES; and SHOW TABLES;.

According to the MySQL manual, infoschema user is a reserved user account in MySQL that is, apparently, used for checking access privileges. This does not tell us how to solve the problem, and Google gave me no useful information.

Solutions

Something must have seriously messed up my root user account. I ended up re-installing MySQL in order to fix the problem, which is something I rarely do as I prefer to find a fix rather than use sledgehammer solutions. Anyway, this was luckily just a local test server, so re-installing was simply the fastest way for me to fix the problem.

Ubuntu users can enter the following commands to re-install MySQL:

sudo apt-get remove --purge mysql*
sudo apt purge mysql*
sudo apt autoremove
sudo apt autoclean
sudo apt install mysql-server

Re-installing is not ideal, so you may want to look into other solutions.

Warning: The following solution is untested.

If the problem occurred after changing the host of the root user, you could attempt changing it back using ALTER USER...

Untested:

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

Links

  1. Reserved Accounts - dev.mysql.com

Comments

Ben

Hi,

Same problem and solution for me. This was on ubuntu 19.10 and fresh install of mysql 8.

apt-get remove mysql-server mysql-client and deleting /etc/mysql /var/lib/mysql did not work.

Only with sudo apt-get remove --purge mysql-server mysql-client mysql-common did it work.

So looks like issue might be related to mysql-common

thanks Jacob for article !