The mysql.session exists but is not correctly configured
Tags: mysql
Published: 2022-09-18 09:17:38

---
title: The mysql.session exists but is not correctly configured
published: true
description: 
tags: mysql
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
---
## 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:

![mysql_upgrade error](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d1zn7dt46vpjph67iiv8.jpg)

**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;
```
![mysql-upgrade-result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cj3o99upe0zbmib0jjom.jpg)

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';
```
![mysql-select](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qeankob78n261ses5lco.jpg)

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
```
![mysql-upgrade-force](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dc8t7u29pz8cdj2i1b0t.jpg)

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';
```
![mysql-session](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/huxbruml1pcw575mivf4.jpg)

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;
```
![mysql-privileges](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/90g8ozymxfzklf4rdoi7.jpg)

After modifying the permissions, execute `mysql_upgrade` again to succeed.
```
/usr/bin/mysql_upgrade --force --upgrade-system-tables
```
![upgrade-system-tables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mkxocavxhs1vhx4mrb19.jpg)

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
```