版本: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 |
+----------------------+ |
04 |
+----------------------+ |
05 |
| innodb_index_stats | |
06 |
| innodb_table_stats | |
08 |
| slave_relay_log_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; |
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; |
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'; |
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.', |
60 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information'; |
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, |
76 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information'; |
(责任编辑:IT) |