MySql Can't find mysql.infoschema after update

Question

This morning my mySQL was automatically updated on my system from version 5.7 to 8.0.11.

After this I couldn't see any of my databases or run any commands on phpMyAdmin. Everything I try to do results on some error involving the mysql.infoschema table.

Most of the errors I get are this: #1449 - The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

I've searched for the error and tried running the command to create the user GRANT ALL ON *.* TO 'someuser'@'%' IDENTIFIED BY 'complex-password'; FLUSH PRIVILEGES; as seen in this answer. But I keep getting syntax errors.

After searching for breaking issues involving versions 5.7 and 8.0.11 I've seen in this site that the system table was moved to InnoDB.

The first storage engine for MySQL - MyISAM and was available in MySQL up to now. MySQL 5.7 still uses MyISAM for the MySQL privilege tables in the MySQL schema but in MySQL they are moved to InnoDB.

I even tried a fresh install of version 8.0.11, so I could import my databases, but I get the same #1449 error when trying to login as root.

Ps. I'm using Fedora 28.

Answer 1

You should execute mysql_upgrade each time you upgrade MySQL.

This is because after certain upgrades or downgrades, tables get changed and you need to (re)create some privileges

mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.