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.
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...
ALTER USER 'root'@'localhost' IDENTIFIED BY '';
- Reserved Accounts - dev.mysql.com