Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to solve 5.6.x InnoDB Error Table mysql.innodb_table_stats not found in MySQL". In daily operation, it is believed that many people have doubts about how to solve the 5.6.x InnoDB Error Table mysql.innodb_table_stats not found problem in MySQL. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the question "how to solve the 5.6.x InnoDB Error Table mysql.innodb_table_stats not found in MySQL"! Next, please follow the editor to study!

[problem description]:

When you check error log, you find a large number of warnings:

[Warning] InnoDB Error Table mysql.innodb_index_stats not found

[Warning] InnoDB Error Table mysql.innodb_table_stats not found

[Warning] InnoDB Error Table mysql.slave_master_info not found

[Warning] InnoDB Error Table mysql.slave_relay_log_info not found

[Warning] InnoDB Error Table mysql.slave_worker_info not found

Or when you open the innodb table, it will print out in err-log:

InnoDB: Error: Table "mysql". "innodb_table_stats" not found.

Error: Fetch of persistent statistics requested for table "{databse_name}". "{table_name}" 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.

[solution]:

See if you can drop table first, and if the table doesn't exist, move on to the next step.

DROP TABLE mysql.innodb_index_stats

DROP TABLE mysql.innodb_table_stats

DROP TABLE mysql.slave_master_info

DROP TABLE mysql.slave_relay_log_info

DROP TABLE mysql.slave_worker_info

Delete the frm files of these five tables in the mysql database under datadir (also delete ibd if any)

Rm-rf $datadir/mysql/innodb_index_stats.*

Rm-rf $datadir/mysql/innodb_table_stats.*

Rm-rf $datadir/mysql/slave_master_info.*

Rm-rf $datadir/mysql/slave_relay_log_info.*

Rm-rf $datadir/mysql/slave_worker_info.*

Recreate the five tables by executing the following statement:

USE mysql

CREATE TABLE `innodb_index_ stats` (

`database_ name` varchar (64) COLLATE utf8_bin NOT NULL

`table_ name` varchar (64) COLLATE utf8_bin NOT NULL

`index_ name` varchar (64) COLLATE utf8_bin NOT NULL

`last_ update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

`stat_ name` varchar (64) COLLATE utf8_bin NOT NULL

`stat_ value` bigint (20) unsigned NOT NULL

`sample_ size` bigint (20) unsigned DEFAULT NULL

`stat_ substitution` varchar (1024) COLLATE utf8_bin NOT NULL

PRIMARY KEY (`database_ name`, `table_ name`, `index_ name`, `stat_ name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

CREATE TABLE `innodb_table_ stats` (

`database_ name` varchar (64) COLLATE utf8_bin NOT NULL

`table_ name` varchar (64) COLLATE utf8_bin 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=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

CREATE TABLE `slave_master_ info` (

`Number_of_ lines` int (10) unsigned NOT NULL COMMENT 'Number of lines in the file.'

`Master_log_ name`text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.'

`Master_log_ Pos` bigint (20) unsigned NOT NULL COMMENT 'The master log position of the last read event.'

`Host` char (64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT''COMMENT' The host name of the master.'

`User_ name`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.'

`User_ password`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.'

`Port` int (10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.'

`Connect_ retry` int (10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.'

`Enabled_ ssl` tinyint (1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.'

`Ssl_ ca`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.'

`Ssl_ capath`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.'

`Ssl_ cert`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.'

`Ssl_ cipher`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.'

`Ssl_ Key`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.'

`Ssl_verify_server_ cert` tinyint (1) NOT NULL COMMENT 'Whether to verify the server certificate.'

`Heartbeat` float NOT NULL

`Bind`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server'

`Ignored_server_ IDs`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs'

`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.'

`Retry_ count` bigint (20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.'

`Ssl_ Crl`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)'

`Ssl_ crlpath`text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files'

`Enabled_auto_ position` tinyint (1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.'

PRIMARY KEY (`Host`, `Port`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information'

CREATE TABLE `slave_relay_log_ info` (

`Number_of_ lines` int (10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.'

`Relay_log_ name`text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.'

`Relay_log_ Pos` bigint (20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.'

`Master_log_ name`text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.'

`Master_log_ Pos` bigint (20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.'

`Sql_ delay` int (11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.'

`Number_of_ workers` int (10) unsigned NOT NULL

`Id` int (10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.'

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information'

CREATE TABLE `slave_worker_ info` (

`Id` int (10) unsigned NOT NULL

`Relay_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Relay_log_ Pos` bigint (20) unsigned NOT NULL

`Master_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Master_log_ Pos` bigint (20) unsigned NOT NULL

`Checkpoint_relay_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Checkpoint_relay_log_ Pos` bigint (20) unsigned NOT NULL

`Checkpoint_master_log_ name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

`Checkpoint_master_log_ Pos` bigint (20) unsigned NOT NULL

`Checkpoint_ seqno` int (10) unsigned NOT NULL

`Checkpoint_group_ size` int (10) unsigned NOT NULL

`Checkpoint_group_ bitmap` blob NOT NULL

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information'

Finally, restart mysqld.

At this point, the study on "how to solve the 5.6.x InnoDB Error Table mysql.innodb_table_stats not found in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.