Knowledge Base

Plesk Update fails: Table ‘mysql.innodb_index_stats’ doesn’t exist in engine

Symptoms

Plesk update fails due to corrupted InnoDB table(s): mysql.innodb_index_stats, mysql.innodb_table_stats, mysql.transaction_registry, mysql.gtid_slave_pos:

Error : Table 'mysql.innodb_index_stats' doesn't exist in engine
...
Error : Table 'mysql.innodb_table_stats' doesn't exist in engine
...
Error : Table 'mysql.transaction_registry' doesn't exist in engine
...
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
One of the errors above also appears on attempt to create a database dump of the mysql database or install SpamAssassin Spam Filter.

Cause

Corrupted InnoDB tables in the mysql database.

Resolution

For Plesk on Linux

Connect to the Plesk server via SSH.
Create a temporary backup directory:

# mkdir -p /root/backup_folder/
Stop the MySQL/MariaDB service:

# service mariadb stop
Move the .frm and .ibd files of the corrupted table(s) that was mentioned in the error message:

# mv /var/lib/mysql/mysql/innodb_index_stats* /root/backup_folder/

# mv /var/lib/mysql/mysql/innodb_table_stats* /root/backup_folder/

# mv /var/lib/mysql/mysql/transaction_registry* /root/backup_folder/

# mv /var/lib/mysql/mysql/gtid_slave_pos.* /root/backup_folder/
Start the MySQL/MariaDB service:

# service mariadb start
Access the Plesk database:

# plesk db
Switch to the MySQL/MariaDB database:

MariaDB [psa]> use mysql;
Remove the table(s) from the mysql database (if exists):

MariaDB [mysql]> drop table innodb_index_stats;

MariaDB [mysql]> drop table innodb_table_stats;

MariaDB [mysql]> drop table transaction_registry;

MariaDB [mysql]> drop table gtid_slave_pos;
Recreate the table:
- innodb_index_stats
  
  MariaDB [mysql]> CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) NOT NULL,
  `table_name` varchar(199) NOT NULL,
  `index_name` varchar(64) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `stat_name` varchar(64) NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0;
- innodb_table_stats
  
  MariaDB [mysql]> CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) NOT NULL,
  `table_name` varchar(199) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0;
- transaction_registry
  
  MariaDB [mysql]> CREATE TABLE `transaction_registry` (
  `transaction_id` bigint(20) unsigned NOT NULL,
  `commit_id` bigint(20) unsigned NOT NULL,
  `begin_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `commit_timestamp` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
  `isolation_level` enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE…