Applicable to:
- Plesk for Linux
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
:CONFIG_TEXT: 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
-
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:
MYSQL_LIN: MariaDB [psa]> use mysql;
-
Remove the table(s) from the mysql database (if exists):
MYSQL_LIN: MariaDB [mysql]> drop table innodb_index_stats;
MYSQL_LIN: MariaDB [mysql]> drop table innodb_table_stats;
MYSQL_LIN: MariaDB [mysql]> drop table transaction_registry;
MYSQL_LIN: MariaDB [mysql]> drop table gtid_slave_pos;
-
Recreate the table:
-
innodb_index_stats
MYSQL_LIN: 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
MYSQL_LIN: 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
MYSQL_LIN: 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; -
gtid_slave_pos
MYSQL_LIN: CREATE TABLE `gtid_slave_pos` (
`domain_id` int(10) unsigned NOT NULL,
`sub_id` bigint(20) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`seq_no` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`domain_id`,`sub_id`))
ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';
-
-
Exit MySQL/MariaDB:
MYSQL_LIN: MariaDB [mysql]> exit
-
Restart the MySQL/MariaDB service:
# service mariadb restart
-
Remove the temporary backup directory:
# rm -f /root/backup_folder/
-
Connect to the Plesk server via RDP.
-
Stop PleskSQLServer service:
C:\> net stop PleskSQLServer
-
Open
%plesk_dir%MySQL\Data\mysql
in Windows Explorer. -
Remove the
.frm
and.ibd
files of the corrupted table(s) that was mentioned in the error message. -
Start PleskSQLServer service:
C:\> net start PleskSQLServer
-
Access the Plesk database:
C:\> plesk db
-
Switch to the MySQL/MariaDB database:
MYSQL_WIN: MariaDB [psa]> use mysql;
-
Remove the table(s) from the mysql database (if exists):
MYSQL_WIN: MariaDB [mysql]> drop table innodb_index_stats;
MYSQL_WIN: MariaDB [mysql]> drop table innodb_table_stats;
MYSQL_WIN: MariaDB [mysql]> drop table transaction_registry;
MYSQL_WIN: MariaDB [mysql]> drop table gtid_slave_pos;
-
Recreate the table(s):
-
innodb_index_stats
MYSQL_WIN: 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
MYSQL_WIN: 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
MYSQL_WIN: 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; -
gtid_slave_pos
MYSQL_WIN: CREATE TABLE `gtid_slave_pos` (
`domain_id` int(10) unsigned NOT NULL,
`sub_id` bigint(20) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`seq_no` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`domain_id`,`sub_id`))
ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';
-
-
Exit MySQL/MariaDB:
MYSQL_WIN: MariaDB [mysql]> exit
-
Restart the MySQL/MariaDB service:
C:\> net stop PleskSQLServer
C:\> net start PleskSQLServer
Comments
0 comments
Please sign in to leave a comment.