One of our MySQL server(5.5.x) crashed but fortunately we have setup the backup of it. I have built the new server with MySQL(5.7.34) and restored the backup on it but when I tried to run any query like creating new user(s) etc got an error something like:
ERROR 1728 (HY000): Cannot load from mysql.db. The table is probably corrupted
This happens due to the schema changes required for different MySQL server versions. The simple fix to this problem recommended by MySQL is to run the mysql_upgrade command from the command line. mysql_upgrade checks all tables across all databases for incompatibilities with the current version of MySQL. mysql_upgrade also upgrades the system tables so that we can take advantage of new privileges or capabilities that might have been added. It supersedes the older mysql_fix_privilege_tables script, which should no longer be used.
[IMPORTANT NOTE] Before running mysql_upgrade command on production server, it’s always a good practice to take a full backup of all the databases first, just in case something goes wrong.
I tried to run mysql_upgrade command from terminal:
mysql_upgrade -uroot -p
After entering the command, got this error message:
Note: I am not mentioning mysql username and password in the command because I am using .my.cnf configuration
mysql_upgrade will perform a weaker verification. If the result is not equal to 1, then mysql_upgrade cannot be executed.
SELECT SUM(count)=3 FROM ( SELECT COUNT(*) as count FROM
mysql.tables_priv WHERE Table_priv='Select' and User='mysql.session' and
Db='mysql' and Table_name='user' UNION ALL SELECT COUNT(*) as count FROM
mysql.db WHERE Select_priv='Y' and User='mysql.session' and
Db='performance_schema' UNION ALL SELECT COUNT(*) as count FROM
mysql.user WHERE Super_priv='Y' and User='mysql.session') as user_priv;
Query to check whether there are multiple mysql.session users in the mysql.user table:
select user ,host from mysql.user where user='mysql.session';
When multiple users are found, keep the user whose Host is listed as localhost, delete the rest, and then execute mysql_upgrade again, this time I run with –force parameter:
mysql_upgrade -uroot -p --force
It didn’t work even with –force parameter
If the above situation does not exist, please check whether the mysql.session user in the mysql.db and mysql.user tables has the Select_priv privilege:
SELECT * FROM `mysql`.`user` WHERE User='mysql.session';
If there is no such information in the db table, you can directly execute the insert statement: