The mysql.session exists but is not correctly configured

The mysql.session exists but is not correctly configured

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:

INSERT INTO `mysql`.`db`(`Host`, `Db`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`, `Trigger_priv`) VALUES ('localhost', 'performance_schema', 'mysql.session', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N');
flush privileges;

After modifying the permissions, execute mysql_upgrade again to succeed.

/usr/bin/mysql_upgrade --force --upgrade-system-tables

After successfully running mysql_upgrade, restart the mysql service so that any changes made to the system tables are ensured to take effect.

systemctl restart mysql