InnoDB: Error: Tablespace for table "mysql"."innodb_table_stats" is missing.

太过爱你忘了你带给我的痛 2024-02-17 20:41 77阅读 0赞

2017-03-30 14:02:20 7fc4fdc8a700  InnoDB: Error: Tablespace for table "mysql"."innodb\_table\_stats" is missing.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Fetch of persistent statistics requested for table "collection"."reason\_logs" 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.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Tablespace for table "mysql"."innodb\_table\_stats" is missing.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Fetch of persistent statistics requested for table "collection"."seats" 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.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Tablespace for table "mysql"."innodb\_table\_stats" is missing.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Fetch of persistent statistics requested for table "collection"."smsgateway" 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.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Tablespace for table "mysql"."innodb\_table\_stats" is missing.  
2017-03-30 14:02:20 7fc4fdc8a700 InnoDB: Error: Fetch of persistent statistics requested for table "collection"."smstemplateparam" 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.  
2017-03-30 14:08:17 13909 \[Warning\] IP address '192.168.1.105' could not be resolved: Name or service not known  
2017-03-30 14:13:54 7fc4f5ffb700 InnoDB: Error: Tablespace for table "mysql"."innodb\_table\_stats" is missing.  
2017-03-30 14:13:54 7fc4f5ffb700 InnoDB: Recalculation of persistent statistics requested for table "collection"."qrtz\_scheduler\_state" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

解决的方法.  
1.删除上述系统表

mysql> use mysql;  
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;

2.删除相关的.frm .ibd文件  
rm -rf innodb\_index\_stats\*  
rm -rf innodb\_table\_stats\*  
rm -rf slave\_master\_info\*  
rm -rf slave\_relay\_log\_info\*  
rm -rf slave\_worker\_info\*

3.重新创建上述系统表

mysql> 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\_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';