MySQL/MariaDB reports the error on a Plesk for Linux server: Cannot load from mysql.<table_name>. The table is probably corrupted – Plesk

Symptoms

• Plesk upgrade fails/completes with the following message:

  ERROR while trying to upgrade APSC SQL database from 11.1.0
  Check the error reason(see log file: /var/log/plesk/install/plesk_17.5.3_installation.log), fix and try again

  In the mentioned above logfile, the following error message can be found:

  ERROR 1548 (HY000) at line 67: Cannot load from mysql.proc. The table is probably corrupted
  ...
  mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)

• Migration completes with this warning message:

  [Migration] Failed to copy content of database 'DB_NAME'
  Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server 'source' (203.0.113.2) with non-zero exit code.
  command: MYSQL_PWD="$(cat)" mysqldump -h localhost -P 3306 -uadmin --quick --quote-names --add-drop-table --default-character-set=utf8 --set-charset --routines hardcore > /tmp/db-dumps/DB_NAME.sql
  exit code: 2
  stdout:
  stderr: mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'DB_NAME'': Cannot load from mysql.proc. The table is probably corrupted (1548)

• A backup task completes with this warning message:

  Warning : mysql "Database"

  Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 2: mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'db_name'': Cannot load from mysql.proc. The table is probably corrupted (1728)

• Operations with databases in phpMyAdmin fail with:

  Errors occurred while executing: mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'db_name'': Cannot load from mysql.proc. The table is probably corrupted (1548)

• The following error message appears in Plesk at Tools & Settings > Database Servers:

  Error: Cannot load from mysql.user. The table is probably corrupted

  and the MySQL/MariaDB (localhost) status icon is grayed out.

Cause

The mysql.<table_name> table is broken or the structure of a column in the table is incorrect.

Resolution

1. Connect to the Plesk server via SSH.

2. Repair the broken table:

   • If mysql.proc is corrupted:

     # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin mysql -Ne"REPAIR TABLE mysql.proc"
     +------------+--------+--------+----+
     | mysql.proc | repair | status | OK |
     +------------+--------+--------+----+

   • If mysql.user is corrupted:

     # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin mysql -Ne"REPAIR TABLE mysql.user"
     +------------+--------+--------+----+
     | mysql.user | repair | status | OK |
     +------------+--------+--------+----+

3. Upgrade mysql system tables:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` /usr/bin/mysql_upgrade --force -uadmin --upgrade-system-tables

   • If the command execution fails with the error below:

     ERROR 1408 (HY000) at line 398: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
     FATAL ERROR: Upgrade failed

     rerun it once again.

   • mysql_upgrade utility is a part of the package mariadb-server-utils. If this package is not installed, get it with the command:

     # yum install mariadb-server-utils.x86_64

4. Restart the MySQL/MariaDB service. The command depends on installed MySQL/MariaDB server and operating system:

   # service mysql restart

   # service mariadb restart