[INFO] Table mysql.innodb_index_stats doesnt exist in engine

[elboriyorker]

Symptoms

• Plesk update fails due to the corrupted InnoDB table(s): mysql.innodb_index_stats, mysql.innodb_table_stats, mysql.transaction_registry:
  
  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
• One of the errors above also appears on attempt to create a database dump of the mysql database.

Cause

Corrupted InnoDB tables in the mysql database. Resolution

[COLOR=#000!important]For Plesk on Linux[/COLOR]

1. Connect to the Plesk server via SSH.
2. Create a temporary backup directory:
   # mkdir -p /root/backup_folder/
3. Stop the MySQL/MariaDB service:
   # service mariadb stop
4. 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/
5. Start the MySQL/MariaDB service:
   # service mariadb start
6. Access the Plesk database:
   # plesk db
7. Switch to the MySQL/MariaDB database:
   MariaDB [psa]> use mysql;
8. 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;
9. 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_n ame`)
     ) 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-READ','SERIALIZABLE') NOT NULL,
     PRIMARY KEY (`transaction_id`),
     UNIQUE KEY `commit_id` (`commit_id`),
     KEY `begin_timestamp` (`begin_timestamp`),
     KEY `commit_timestamp` (`commit_timestamp`,`transaction_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0;
10. Exit MySQL/MariaDB:
    MariaDB [mysql]> exit
11. Restart the MySQL/MariaDB service:
    # service mariadb restart
12. Update Plesk.
13. Remove the temporary backup directory:
    # rm -f /root/backup_folder/