Plesk upgrade failed because of missing tables in mysql database

Created:

2017-04-30 16:42:30 UTC

Modified:

2017-08-16 16:59:24 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Plesk upgrade failed because of missing tables in mysql database

Applicable to:

  • Plesk Onyx for Linux

Symptoms

  • Plesk upgrade failed because of missing tables in mysql database. Output of FOUND_ROWS() less than 30:

    # plesk db
    mysql> use mysql;
    mysql> SHOW TABLES; SELECT FOUND_ROWS();

  • MySQL service cannot be started after failed Plesk upgrade with the following errors in /var/log/mysql/error.log :

    2017-06-13T10:26:35.045724Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
    2017-06-13T10:26:35.050157Z 0 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
    2017-06-13T10:26:35.050184Z 0 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mysql`.`gtid_executed` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
    2017-06-13T10:26:35.050813Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
    2017-06-13T10:26:35.050878Z 0 [ERROR] Aborting

Cause

InnoDB got corrupted. As a result, a lot of tables are missing from mysql database.

Resolution

Restore tables for mysql database from the pre-upgrade or daily dump that are stored in /var/lib/psa/dumps directory by default:

1. If MySQL service cannot be started, add skip-grant-tables option to [mysqld] section in /etc/my.cnf file (on Debian based distributions the path is /etc/mysql/my.cnf ):

CONFIG_TEXT: [mysqld]
skip-grant-tables

and start MySQL service:

# service mysql start

2. Make sure that MySQL service is running, back up mysql database and restore it from the latest valid dump:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin --skip-extended-insert mysql > /root/mysql.dump.`date +%F.%s`.sql
# gunzip /var/lib/psa/dumps/mysql.daily.dump.0.gz
# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin -Dmysql < /var/lib/psa/dumps/mysql.daily.dump.0

3. If MySQL service is running with skip-grant-tables option, comment it from /etc/my.cnf file (on Debian based distributions the path is /etc/mysql/my.cnf )

CONFIG_TEXT: [mysqld]
#skip-grant-tables

and restart MySQL service:

# service mysql restart

Have more questions? Submit a request
Please sign in to leave a comment.