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…
Mysql was started:
/usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700
but when I’m trying to do something:
ERROR 1548 (HY000) at line 1: Cannot load from mysql.proc. The table is probably corrupted
How to fix it?
$ mysql -V
mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2
$ lsb_release -a
Distributor ID: Ubuntu
Description: Ubuntu 11.10
Release: 11.10
Codename: oneiric
$ sudo mysql_upgrade -uroot -p<password> --force
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log
Error : You can't use locks with log tables.
status : OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Running 'mysql_fix_privilege_tables'...
OK
$ mysqlcheck --port=3700 --socket=/srv/mysql/sockets/mysql-my-env.sock -A -udata_owner -pdata_owner
<all tables> OK
UPD1: for example I’m trying to remove procedure:
mysql> DROP PROCEDURE IF EXISTS mysql.myproc;
ERROR 1548 (HY000): Cannot load from mysql.proc. The table is probably corrupted
mysql>
UPD2:
mysql> REPAIR TABLE mysql.proc;
+------------+--------+----------+-----------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+-----------------------------------------------------------------------------------------+
| mysql.proc | repair | error | 1 when fixing table |
| mysql.proc | repair | Error | Can't change permissions of the file '/srv/mysql/myDB/mysql/proc.MYD' (Errcode: 1) |
| mysql.proc | repair | status | Operation failed |
+------------+--------+----------+-----------------------------------------------------------------------------------------+
3 rows in set (0.04 sec)
This is strange, because:
$ ls -l /srv/mysql/myDB/mysql/proc.MYD
-rwxrwxrwx 1 mysql root 3983252 2012-02-03 22:51 /srv/mysql/myDB/mysql/proc.MYD
UPD3:
$ ls -la /srv/mysql/myDB/mysql
total 8930
drwxrwxrwx 2 mysql root 2480 2012-02-21 13:13 .
drwxrwxrwx 13 mysql root 504 2012-02-21 19:01 ..
-rwxrwxrwx 1 mysql root 8820 2012-02-20 15:50 columns_priv.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 columns_priv.MYD
-rwxrwxrwx 1 mysql root 4096 2012-02-20 15:50 columns_priv.MYI
-rwxrwxrwx 1 mysql root 9582 2012-02-20 15:50 db.frm
-rwxrwxrwx 1 mysql root 8360 2011-12-08 02:14 db.MYD
-rwxrwxrwx 1 mysql root 5120 2012-02-20 15:50 db.MYI
-rwxrwxrwx 1 mysql root 54 2011-11-12 15:42 db.opt
-rwxrwxrwx 1 mysql root 10223 2012-02-20 15:50 event.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 event.MYD
-rwxrwxrwx 1 mysql root 2048 2012-02-20 15:50 event.MYI
-rwxrwxrwx 1 mysql root 8665 2012-02-20 15:50 func.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 func.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 func.MYI
-rwxrwxrwx 1 mysql root 8700 2012-02-20 15:50 help_category.frm
-rwxrwxrwx 1 mysql root 21497 2011-11-12 15:42 help_category.MYD
-rwxrwxrwx 1 mysql root 3072 2012-02-20 15:50 help_category.MYI
-rwxrwxrwx 1 mysql root 8612 2012-02-20 15:50 help_keyword.frm
-rwxrwxrwx 1 mysql root 88650 2011-11-12 15:42 help_keyword.MYD
-rwxrwxrwx 1 mysql root 16384 2012-02-20 15:50 help_keyword.MYI
-rwxrwxrwx 1 mysql root 8630 2012-02-20 15:50 help_relation.frm
-rwxrwxrwx 1 mysql root 8874 2011-11-12 15:42 help_relation.MYD
-rwxrwxrwx 1 mysql root 16384 2012-02-20 15:50 help_relation.MYI
-rwxrwxrwx 1 mysql root 8770 2012-02-20 15:50 help_topic.frm
-rwxrwxrwx 1 mysql root 414320 2011-11-12 15:42 help_topic.MYD
-rwxrwxrwx 1 mysql root 20480 2012-02-20 15:50 help_topic.MYI
-rwxrwxrwx 1 mysql root 9510 2012-02-20 15:50 host.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 host.MYD
-rwxrwxrwx 1 mysql root 2048 2012-02-20 15:50 host.MYI
-rwxrwxrwx 1 mysql root 8554 2011-11-12 15:42 innodb_monitor.frm
-rwxrwxrwx 1 mysql root 98304 2011-11-12 15:55 innodb_monitor.ibd
-rwxrwxrwx 1 mysql root 8592 2012-02-20 15:50 inventory.frm
-rwxrwxrwx 1 mysql root 76 2011-11-12 15:42 inventory.MYD
-rwxrwxrwx 1 mysql root 2048 2012-02-20 15:50 inventory.MYI
-rwxrwxrwx 1 mysql root 8778 2012-02-20 15:50 ndb_binlog_index.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 ndb_binlog_index.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 ndb_binlog_index.MYI
-rwxrwxrwx 1 mysql root 8586 2012-02-20 15:50 plugin.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 plugin.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 plugin.MYI
-rwxrwxrwx 1 mysql root 9996 2012-02-20 15:50 proc.frm
-rwxrwxrwx 1 mysql root 3983252 2012-02-03 22:51 proc.MYD
-rwxrwxrwx 1 mysql root 36864 2012-02-21 13:23 proc.MYI
-rwxrwxrwx 1 mysql root 8875 2012-02-20 15:50 procs_priv.frm
-rwxrwxrwx 1 mysql root 1700 2011-11-12 15:42 procs_priv.MYD
-rwxrwxrwx 1 mysql root 8192 2012-02-20 15:50 procs_priv.MYI
-rwxrwxrwx 1 mysql root 3977704 2012-02-21 13:23 proc.TMD
-rwxrwxrwx 1 mysql root 8800 2012-02-20 15:50 proxies_priv.frm
-rwxrwxrwx 1 mysql root 693 2011-11-12 15:42 proxies_priv.MYD
-rwxrwxrwx 1 mysql root 5120 2012-02-20 15:50 proxies_priv.MYI
-rwxrwxrwx 1 mysql root 8838 2012-02-20 15:50 servers.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 servers.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 servers.MYI
-rwxrwxrwx 1 mysql root 8955 2012-02-20 15:50 tables_priv.frm
-rwxrwxrwx 1 mysql root 5957 2011-11-12 15:42 tables_priv.MYD
-rwxrwxrwx 1 mysql root 8192 2012-02-20 15:50 tables_priv.MYI
-rwxrwxrwx 1 mysql root 8636 2012-02-20 15:50 time_zone.frm
-rwxrwxrwx 1 mysql root 8624 2012-02-20 15:50 time_zone_leap_second.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 time_zone_leap_second.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 time_zone_leap_second.MYI
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 time_zone.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 time_zone.MYI
-rwxrwxrwx 1 mysql root 8606 2012-02-20 15:50 time_zone_name.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 time_zone_name.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 time_zone_name.MYI
-rwxrwxrwx 1 mysql root 8686 2012-02-20 15:50 time_zone_transition.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 time_zone_transition.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 time_zone_transition.MYI
-rwxrwxrwx 1 mysql root 8748 2012-02-20 15:50 time_zone_transition_type.frm
-rwxrwxrwx 1 mysql root 0 2011-11-12 15:42 time_zone_transition_type.MYD
-rwxrwxrwx 1 mysql root 1024 2012-02-20 15:50 time_zone_transition_type.MYI
-rwxrwxrwx 1 mysql root 10630 2012-02-20 15:50 user.frm
-rwxrwxrwx 1 mysql root 5456 2011-11-12 21:01 user.MYD
-rwxrwxrwx 1 mysql root 4096 2012-02-20 15:50 user.MYI
MySQL will sometimes return the error message “Cannot load from mysql.proc. The table is probably corrupted”. This happens due to schema changes required for different MySQL server versions. The simple fix to this problem is to run the mysql_upgrade command from the command line.
About mysql_upgrade
mysql_upgrade examines all tables across 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.
mysql_upgrade should be executed each time you upgrade MySQL. It supersedes the older mysql_fix_privilege_tables script, which should no longer be used.
mysql_upgrade executes the following commands to check and repair tables and to upgrade the system tables:
mysqlcheck --all-databases --check-upgrade --auto-repair
mysql < fix_priv_tables
Run mysql_upgrade From Command Line
To use mysql_upgrade, make sure your MySQL server is running and then invoke mysql_upgrade from the command line tool:
mysql_upgrade -uroot -p --force
You should then be prompted to enter the MySQL root’s password and mysql_upgrade will check all the databases and tables and fix them where appropriate. You may need to specify the full path to the above command if it’s not in the shell’s search path.
On Debian 6 it should be loacted at:
/usr/bin/mysql_upgrade -uroot -p --force
On Mac’s MAMP the default path is:
/Applications/MAMP/Library/bin/mysql_upgrade -uroot -p --force
On Windows it’ll be where MySQL is installed and contained in the bin subdirectory. By default it should be located at:
"C:Program FilesMySQLMySQL Server[*CHANGE TO MySQL SERVER*]binmysqladmin" -u root shutdown
VICTORY!
After running mysql_upgrade, stop the server and restart it so that any changes made to the system tables are ensured to take effect.
All checked and repaired tables are marked with the current MySQL version number. This ensures that next time you run mysql_upgrade with the same version of the server, it can tell whether there is any need to check or repair the table again.
During a Debian upgrade (Buster to Bullseye), MariaDB was upgraded.
After MariaDB was upgraded from 10.3 to 10.5, the next day the following errors were shown from the daily mysqldump script:
root@bullseye:~# /root/scripts/backup-mysql.sh
mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘db1»: Cannot load from mysql.proc. The table is probably corrupted (1728)
mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘db2»: Cannot load from mysql.proc. The table is probably corrupted (1728)
mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘mysql»: Cannot load from mysql.proc. The table is probably corrupted (1728)
These errors are showing up due to changes inside the internal mysql database. For example in MariaDB 10.4 the mysql.proc table has changed its storage engine from MyISAM to Aria. This and other potential changes to the mysql database require a «mysql database upgrade».
To fix this, the mysql database needs to be upgraded manually using the mysql_upgrade command:
root@bullseye:~# mysql_upgrade -u root -p
Enter password: **********
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry OK
mysql.user OK
Phase 2/7: Installing used storage engines… Skipped
Phase 3/7: Fixing views
Phase 4/7: Running ‘mysql_fix_privilege_tables’
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
db1
db2
performance_schema
Phase 7/7: Running ‘FLUSH PRIVILEGES’
OK
After the upgrade, the mysqldump script works again and the errors are gone:
root@bullseye:~# /root/scripts/backup-mysql.sh && echo $?
0
Add a comment
Show form to leave a comment
Comments (newest first)
Ken from wrote on Jun 1st, 2022:
Thanks for the info, it fixes the issue