mysql错误:表“mysql”。没有找到“innodb_table_stats”

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?

如何修正这个错误?

5 个解决方案

#1

Check out this link:

查看这个链接:

http://bugs.mysql.com/file.php?id=19725

This solved my problem. Since its a direct, download, here is the code.

这解决了我的问题。由于它是直接下载，这里是代码。

/* 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..

/* windows 7机器上的MySQL 5.6.10安装程序问题的临时修复。我在一个干净安装的MySql上完成了这个过程，它对我来说是有用的，至少它阻止了日志中出现的几行innodb错误以及使用临时的innodb表。所以，你要自己承担风险。

drop these tables from mysql: innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info

将这些表从mysql中删除:innodb_index_stats innodb_table_stats slave_master_info slave_relay_log_info slave_worker_info

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

删除上面表的所有.frm & .ibd。

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

运行此文件以重新创建上面的表(源5大表.sql)。

restart mysqld. Cheers, CNL */

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

#2

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,

祝你好运,

#3

Read this mysql doc to learn more about Persistent Statistics.

请阅读本mysql文档以了解更多有关持久性统计信息。

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

要禁用此特性，请将innodb_stats_persistent = 0添加到my中。cfg并重启mysql服务器。

#4

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

#5

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

#1