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.

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

我们知道，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]$ ls
bulgarian dictionary.txt estonian greek italian mysql-log-rotate mysql_system_tables.sql polish serbian ukrainian
charsets dutch fill_help_tables.sql hungarian japanese mysql_security_commands.sql mysql_test_data_timezone.sql portuguese slovak uninstall_rewriter.sql
czech english french innodb_memcached_config.sql korean mysql_sys_schema.sql norwegian romanian spanish
danish 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 names
You can turn off this feature to get a quicker startup with -A
Database 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

近期更新的文章：

《最近碰到的几个问题》

《JDBC SSL连接MySQL》

《exp和expdp几种常见的使用场景操作介绍》

文章分类和索引：

《公众号800篇文章分类和索引》