mysql 错误：找不到表“mysql”.“innodb_table_stats”

mysql error: Table "mysql"."innodb_table_stats" not found

提问by user121196

I'm getting the following error in mysql 5.6 community edition:

我在 mysql 5.6 社区版中收到以下错误：

: Error: 
Table "mysql"."innodb_table_stats"
 not found. InnoDB: Recalculation 
of persistent statistics requested for table "mydatabase"."mytable" 
but the required persistent statistics storage is not present or is corrupted. 
Using transient stats instead.

how do I fix this error?

我该如何解决这个错误？

回答by Mad Dog Tannen

This solved my problem.

这解决了我的问题。

/* temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines. I did the procedure on a clean installed MySql, and it worked for me, at least it stopped lines of innodb errors in the log and the use of transient innodb tables. So, do it at your own risk..

1. drop these tables from mysql: innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info

2. delete all .frm & .ibd of the tables above.

3. run this file to recreate the tables above (source five-tables.sql).

4. restart mysqld. Cheers, CNL */

/* 临时修复 Windows 7 机器上 MySQL 5.6.10 的 Windows 安装程序问题。我在一个干净安装的 MySql 上做了这个过程，它对我有用，至少它停止了日志中的 innodb 错误行和瞬态 innodb 表的使用。因此，请自行承担风险。

1. 从 mysql 中删除这些表： innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info

2. 删除上表的所有 .frm 和 .ibd。

3. 运行此文件以重新创建上面的表（源五表.sql）。

4. 重启mysqld。干杯，CNL */

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_description` 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';

回答by Mark

I ran into this issue on a production server recently and the most common answers I found involving dropping tables, deleting files etc. made me feel a little uneasy!

我最近在生产服务器上遇到了这个问题，我发现的最常见的答案涉及删除表、删除文件等，让我感到有点不安！

So I thought it might be useful to point out for people reading this in future that in my case the actual cause of the error was my root partition filling up. I had been making a mysql dump at the time and saved it to the wrong place. So the solution was just to free up some disk space and restart the service.

因此，我认为向将来阅读本文的人指出，在我的情况下，错误的实际原因是我的根分区已满，这可能很有用。我当时一直在做一个 mysql 转储并将它保存到错误的地方。所以解决方案只是释放一些磁盘空间并重新启动服务。

It might be an idea to check that this is not the case for you before moving onto the standard solutions which involve messing around with the structure of MYSQL and have the potential to be a little riskier.

在转向涉及 MYSQL 结构的标准解决方案之前，检查这对您来说不是这种情况可能是一个想法，并且可能会有点风险。

Good Luck,

祝你好运，

回答by Brahma

Read this mysql docto learn more about Persistent Statistics.

阅读此 mysql 文档以了解有关Persistent Statistics.

To disable this feature, add innodb_stats_persistent = 0to my.cfgand restart your mysql server.

要禁用此功能，添加innodb_stats_persistent = 0到my.cfg并重新启动MySQL服务器。

回答by Thoman

same issue but crash table 'gtid_slave_pos', Create gtid_slave_pos

同样的问题，但崩溃表 ' gtid_slave_pos'，创建gtid_slave_pos

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';

回答by glowkeeper

This turned out to be a permission problem for me. Since this was on a development machine where permissions are not locked down, I was able to fix it easily enough:

结果证明这对我来说是一个许可问题。由于这是在权限未锁定的开发机器上，我能够很容易地修复它：

sudo chmod -R 775 /usr/local/mysql/data

...if that solves it for you, then great. But if the problem is on a production machine, you may wish to investigate permissions a little more thoroughly than my solution.

...如果这为您解决了问题，那就太好了。但是如果问题出在生产机器上，您可能希望比我的解决方案更彻底地调查权限。

maybe you would like to try this instead:

也许你想试试这个：

sudo chown mysql.mysql -R /usr/local/mysql/data

回答by micah94

If you're running Debian Linux:

如果您运行的是 Debian Linux：

My solution to this problem was after dropping the mysql.innodb_table_stats file and removing the associated .frm and .ibd files, I ran this:

我对这个问题的解决方案是在删除 mysql.innodb_table_stats 文件并删除相关的 .frm 和 .ibd 文件后，我运行了这个：

dpkg-reconfigure mariadb-server-10.1

It recreates the innodb_* tables for you but for whatever reason had trouble doing it with the files already there (from a previous install).

它会为您重新创建 innodb_* 表，但无论出于何种原因，使用已经存在的文件（来自之前的安装）都遇到了麻烦。

I consider this safer as the columns and their names may have changed since the answer above was written.

我认为这更安全，因为自从上面的答案写出来后，列和它们的名字可能已经改变了。