Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

Question

Whenever I try to do a DB query with a particular DB user (myuser) I get the following error:

Access denied for user 'myuser'@'localhost' to database 'mydatabase' [1044]

I decided to check the grants for that user and saw that one of the grants could maybe benefit from an update so I tried to update it and got the following error:

Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted

I Google'd the error and found Cannot load from mysql.proc. The table is probably corrupted , which recommends doing mysql_upgrade -u root -pxxx. I tried that and got this error:

Error occurred: The mysql.session exists but is not correctly configured. The mysql.session needs SELECT privileges in the performance_schema database and the mysql.db table and also SUPER privileges.

I Google that error and found MySQL Upgrade Issue but the answer there isn't applicable - there appears to be just one mysql.session user when I do SELECT * FROM mysql.user;. So I figured maybe I'll just create the GRANTs myself:

GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`db` TO 'mysql.session'@'localhost';
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';

Only problem: when I attempt to do so I get the same "Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted" that I got before.

My question is... what can I do to fix this? I have a full DB backup that I restored from without issue a few months ago and if there have been any DB changes that have occurred in the DB since then I'm okay with losing. Maybe my best option is to simply uninstall MySQL and re-install it? And if that is the case then are there any steps I can take to prevent this issue from cropping up again?

I'm running MySQL 5.7.25-0ubuntu0.16.04.2 on Ubuntu 16.04.5 LTS on a Digital Ocean droplet.

Any ideas?

edit: I also get this error when I try to create a new MySQL user:

CREATE USER username IDENTIFIED BY 'password';

I tried mysqlcheck -uroot mysql db and got this back:

mysql.db                                           OK

I tried ls -latr /var/lib/mysql/mysql/db* and got this back:

-rw-r----- 1 mysql mysql   65 Nov  8 03:55 db.opt
-rw-r----- 1 mysql mysql  440 Nov 10 17:51 db.MYD
-rw-r----- 1 mysql mysql 5120 Feb 11 11:54 db.MYI
-rw-r----- 1 mysql mysql 9582 Feb 11 11:54 db.frm

I note that there's not an *.ibd file idk.

SELECT * FROM mysql.db, while logged in as both SSH root / MySQL, works without issue, but all the "Error Code: 1728. Cannot load from mysql.db. The table is probably corrupted" errors are also coming to me while logged in as SSH root / MySQL root so idk.

edit2: I can't even do apt-get remove mysql-server as it gives me the same "mysql.session exists but is not correctly configured" that I was getting before...

Answer 1

I have seen this error many times when installing earlier versions of MySQL 5.7. I am stunned you are still getting this error.

Prior to MySQL 8.0, some of the tables in the mysql schema are MyISAM.

Unfortunately, MyISAMtables get corrupt very easily.

EXAMPLE: The header of the .MYD file holds the count of open file handles. If a MyISAM table is closed and has a nonzero value for open file handles, the MyISAM storage engine considers the table corrupt.

Solution: Just run the following command:

mysql> REPAIR TABLE mysql.db;

I suggested this before : Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

Answer 2

Late to the party!! if nothing works then you can do the below:

For the below tables, copy the table structure from any of the MySQL 8.0 successful installations and rename these tables as _bkp and create freshly with the copied structure and insert the data back from _bkp tables one by one and test.

mysql.db
mysql.columns_priv
mysql.tables_priv 
mysql.procs_priv

This worked for me.. cheers!!