mysql 5.6.x error log InnoDB: Error: Table “innodb_table_stats”not found解决办法
时间:2016-04-11 22:15 来源:linux.it.net.cn 作者:IT
版本:mysql 5.6.x
错误提示:
InnoDB: Cannot open table mysql/innodb_table_stats from the internal data dictionary of InnoDB thou
gh the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve
the problem.
出现这个提示总结起来有两种情况，一、mysql自带数据库mysql中Innodb引擎表损坏，二、从Mysql 5.5.x升级至5.6.x
MySQL 5.6的ibdata1表空间包含了5个InnoDB基础表,如下:
01
mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';
02
+----------------------+
03
| table_name |
04
+----------------------+
05
| innodb_index_stats |
06
| innodb_table_stats |
07
| slave_master_info |
08
| slave_relay_log_info |
09
| slave_worker_info |
10
+----------------------+
11
5 rows in set (0.00 sec)
在MySQL 5.6之前,如果关闭MySQL后删除ibdata1,再重新启动MySQL的时候ibdata1会被重新创建. 但从MySQL 5.6开始,这5个表不会被重建.即使删除了ibdata1,下面的10个文件仍然保留在/data/mysql/mysql中(假如datadir = /data/mysql/):
innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd
解决办法：删除/data/mysql/mysql/目录下5个数据表的表结构及数据文件，进入命令行中执行FLUSH TABLES;然后重新创建5个数据表，再接着查看mysql错误日志发现提示已经消失。
5个数据表的sql
01
CREATE TABLE IF NOT EXISTS `innodb_index_stats` (
02
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
03
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
04
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
05
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
06
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
07
`stat_value` bigint(20) unsigned NOT NULL,
08
`sample_size` bigint(20) unsigned DEFAULT NULL,
09
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
10
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
11
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
12
13
CREATE TABLE IF NOT EXISTS `innodb_table_stats` (
14
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
15
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
16
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
17
`n_rows` bigint(20) unsigned NOT NULL,
18
`clustered_index_size` bigint(20) unsigned NOT NULL,
19
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
20
PRIMARY KEY (`database_name`,`table_name`)
21
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
22
23
CREATE TABLE IF NOT EXISTS `slave_master_info` (
24
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
25
`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.',
26
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
27
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
28
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
29
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
30
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
31
`Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
32
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
33
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
34
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
35
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
36
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
37
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
38
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
39
`Heartbeat` float NOT NULL,
40
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
41
`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',
42
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
43
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
44
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
45
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
46
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
47
PRIMARY KEY (`Host`,`Port`)
48
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
49
50
CREATE TABLE IF NOT EXISTS `slave_relay_log_info` (
51
`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.',
52
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
53
`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
54
`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.',
55
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
56
`Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
57
`Number_of_workers` int(10) unsigned NOT NULL,
58
`Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
59
PRIMARY KEY (`Id`)
60
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
61
62
CREATE TABLE IF NOT EXISTS `slave_worker_info` (
63
`Id` int(10) unsigned NOT NULL,
64
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
65
`Relay_log_pos` bigint(20) unsigned NOT NULL,
66
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
67
`Master_log_pos` bigint(20) unsigned NOT NULL,
68
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
69
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
70
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
71
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
72
`Checkpoint_seqno` int(10) unsigned NOT NULL,
73
`Checkpoint_group_size` int(10) unsigned NOT NULL,
74
`Checkpoint_group_bitmap` blob NOT NULL,
75
PRIMARY KEY (`Id`)
76
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
(责任编辑：IT)
版本:mysql 5.6.x 错误提示: InnoDB: Cannot open table mysql/innodb_table_stats from the internal data dictionary of InnoDB thou gh the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 出现这个提示总结起来有两种情况，一、mysql自带数据库mysql中Innodb引擎表损坏，二、从Mysql 5.5.x升级至5.6.x MySQL 5.6的ibdata1表空间包含了5个InnoDB基础表,如下:
在MySQL 5.6之前,如果关闭MySQL后删除ibdata1,再重新启动MySQL的时候ibdata1会被重新创建. 但从MySQL 5.6开始,这5个表不会被重建.即使删除了ibdata1,下面的10个文件仍然保留在/data/mysql/mysql中(假如datadir = /data/mysql/): 解决办法：删除/data/mysql/mysql/目录下5个数据表的表结构及数据文件，进入命令行中执行FLUSH TABLES;然后重新创建5个数据表，再接着查看mysql错误日志发现提示已经消失。 5个数据表的sql
(责任编辑：IT) |