如何解决MySQL数据字典提示1146不存在的问题

最近某套MySQL因为磁盘挂载问题，异常宕机，拉起后，数据库能正常访问了，但是在error.log一直提示这个错误，

[ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.2021-09-03T08:26:52.446564Z 2 [ERROR] InnoDB: Fetch of persistent statistics requested for table `jira`.`clusteredjob`       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.

从提示来看，mysql.innodb_table_stats这张表是不存在，但是执行了show tables显式存在这张表，

mysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+...| innodb_index_stats        || innodb_table_stats        |...+---------------------------+31 rows in set (0.00 sec)

然而看他的建表语句，就提示了错误，

mysql> show create table innodb_table_stats;ERROR 1146 (42S02): Unknown error 1146

1146的错误，表不存在，

P.S.

https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html

有点懵了，这张表到底存在不存在？

我们知道，MySQL的innodb引擎下，默认情况，每张表都对应本地的一组文件，进入数据路径，发现这张innodb_table_stats只有frm结构文件，并没有ibd数据文件

[mysql@bisal mysql]$ ls -l innodb*-rwxrwxrwx 1 mysql mysql 12982 Jan  6  2020 innodb_index_stats.frm-rwxrwxrwx 1 mysql mysql 98304 Jan  6  2020 innodb_index_stats.ibd-rwxrwxrwx 1    27    27  8830 May  8  2018 innodb_table_stats.frm

推测可能是因为当前数据文件所在磁盘之前非常规卸载，手工挂载，导致文件异常，物理文件已经被删除了，只剩结构文件，从文件名称看，这个应该是存储表统计信息的数据字典，原则上，不影响系统运行，因此数据库可访问但是当需要用到表的统计信息时，因为表实际已经被删除，所以提示错误。针对这种情况，看了一些资料，可以通过手工重建的操作，恢复数据字典。

首先到这个路径，

cd /usr/share/mysql

其中存储了很多的脚本，

[mysql@bisal mysql]$ lsbulgarian  dictionary.txt   estonian              greek                        italian   mysql-log-rotate              mysql_system_tables.sql       polish      serbian  ukrainiancharsets   dutch            fill_help_tables.sql  hungarian                    japanese  mysql_security_commands.sql   mysql_test_data_timezone.sql  portuguese  slovak   uninstall_rewriter.sqlczech      english          french                innodb_memcached_config.sql  korean    mysql_sys_schema.sql          norwegian                     romanian    spanishdanish     errmsg-utf8.txt  german                install_rewriter.sql         magic     mysql_system_tables_data.sql  norwegian-ny                  russian     swedish

可以打开mysql_system_tables_data.sql，其中包含了innodb_table_stats这张表的定义

SET @create_innodb_table_stats="CREATE TABLE IF NOT EXISTS innodb_table_stats (        database_name                   VARCHAR(64) NOT NULL,        table_name                      VARCHAR(64) NOT NULL,        last_update                     TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,        n_rows                          BIGINT UNSIGNED NOT NULL,        clustered_index_size            BIGINT UNSIGNED NOT NULL,        sum_of_other_index_sizes        BIGINT UNSIGNED NOT NULL,        PRIMARY KEY (database_name, table_name)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0";

但是执行提示，还是说这张表不存在，即使执行了drop操作，还提示错误，

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed
mysql> CREATE TABLE innodb_table_stats (    ->         database_name                   VARCHAR(64) NOT NULL,    ->         table_name                      VARCHAR(64) NOT NULL,    ->         last_update                     TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->         n_rows                          BIGINT UNSIGNED NOT NULL,    ->         clustered_index_size            BIGINT UNSIGNED NOT NULL,    ->         sum_of_other_index_sizes        BIGINT UNSIGNED NOT NULL,    ->         PRIMARY KEY (database_name, table_name)    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;ERROR 1146 (42S02): Unknown error 1146

此时，我们就可以用到运维领域的第一法宝 – 重启，再次执行，

mysql> CREATE TABLE innodb_table_stats (    ->         database_name                   VARCHAR(64) NOT NULL,    ->         table_name                      VARCHAR(64) NOT NULL,    ->         last_update                     TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->         n_rows                          BIGINT UNSIGNED NOT NULL,    ->         clustered_index_size            BIGINT UNSIGNED NOT NULL,    ->         sum_of_other_index_sizes        BIGINT UNSIGNED NOT NULL,    ->         PRIMARY KEY (database_name, table_name)    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;Query OK, 0 rows affected (0.01 sec)

见证奇迹的时刻，show tables就出现了这张表，

mysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+...| innodb_index_stats        || innodb_table_stats        |...+---------------------------+31 rows in set (0.00 sec)

查看表的定义

mysql> desc innodb_table_stats;+--------------------------+---------------------+------+-----+-------------------+-----------------------------+| Field                    | Type                | Null | Key | Default           | Extra                       |+--------------------------+---------------------+------+-----+-------------------+-----------------------------+| database_name            | varchar(64)         | NO   | PRI | NULL              |                             || table_name               | varchar(64)         | NO   | PRI | NULL              |                             || last_update              | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP || n_rows                   | bigint(20) unsigned | NO   |     | NULL              |                             || clustered_index_size     | bigint(20) unsigned | NO   |     | NULL              |                             || sum_of_other_index_sizes | bigint(20) unsigned | NO   |     | NULL              |                             |+--------------------------+---------------------+------+-----+-------------------+-----------------------------+6 rows in set (0.00 sec)

物理文件同时存在了innodb_table_stats的两个文件，

[mysql@jf-vra-app2390 mysql]$ ls -l innodb*-rwxrwxrwx 1 mysql mysql  12982 Jan  6  2020 innodb_index_stats.frm-rwxrwxrwx 1 mysql mysql 131072 Sep  3 16:29 innodb_index_stats.ibd-rw-r----- 1 mysql mysql   8830 Sep  3 16:27 innodb_table_stats.frm-rw-r----- 1 mysql mysql  98304 Sep  3 16:29 innodb_table_stats.ibd