InnoDB: Error: Table "mysql"."innodb_table_stats" Not Found.

jiawang

APPLIES TO:
MySQL Server - Version 5.6 and later
Information in this document applies to any platform.
SYMPTOMS
The MySQL error log contains an error related to missing InnoDB table : innodb_table_stats.

This error is found in the error log :

InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

The table does not exist when being checked :

mysql> check table mysql.innodb_table_stats\G
*************************** 1. row ***************************
Table: mysql.innodb_table_stats
    Op: check
Msg_type: Error
Msg_text: Table 'mysql.innodb_table_stats' doesn't exist
*************************** 2. row ***************************
Table: mysql.innodb_table_stats
    Op: check
Msg_type: status
Msg_text: Operation failed
2 rows in set (0.00 sec)

But the innodb_table_stats.frm and innodb_table_stats.ibd exist in the mysql directory.

use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)
CHANGES
The error occurs after the installation of MySQL 5.6. It did not occur in MySQL 5.5.

MySQL 5.6 introduced persistent statistics and this table innodb_table_stats stores the persistent statistics.

CAUSE
For some reason, the shared table space ibdata1 that contains the InnoDB data dictionary does not have entries for existing tables in the mysql database.

Namely :

innodb_index_stats.ibd
slave_master_info.ibd
slave_worker_info.ibd
innodb_table_stats.ibd
slave_relay_log_info.ibd

Possible reasons are listed here :

The innodb_data_filepath configuration variable was changed after the installation : Bug 16418661 - CHANGING NAME IN FOR INNODB_DATA_FILE_PATH SHOULD NOT SUCCEED WITH LOG FILES
The data directory was not properly cleared : Bug 16486155 - CLEARING INNODB DATA FILES IN 5.6
There was an installer problem or at least during the clean install : BUG 15850498 - MYSQL SYSTEM TABLES INNODB_TABLE_STATS,SLAVE_MASTER_INFO NOT ACCESSIBLE ON CLEAN
The InnoDB data dictionary was missing the table while it exists on disk.
The DBA removed the InnoDB system tablespace and log files without removing the InnoDB tables in the mysql schema.
SOLUTION
Solution:

Backup those ibd and related frms, or make sure you have a backup of the database.
Drop the tables in the mysql database :  
mysql> use mysql;

mysql> drop table innodb_index_stats ;
ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats'
mysql> drop table innodb_table_stats;
ERROR 1051 (42S02): Unknown table 'mysql.innodb_table_stats'
mysql> drop table slave_master_info;
ERROR 1051 (42S02): Unknown table 'mysql.slave_master_info'
mysql> drop table slave_relay_log_info;
ERROR 1051 (42S02): Unknown table 'mysql.slave_relay_log_info'
mysql> drop table slave_worker_info;
ERROR 1051 (42S02): Unknown table 'mysql.slave_worker_info
Note : it is normal to get an error message : the drop will remove the frm file, that contains the table's definition.  This file is read by mysqld to list the tables (show tables). It explains why the table is still visible while it "does not exist" because it does not exist in the InnoDB engine.
Move the 5 ibd from the mysql directory
Run mysql_upgrade  
bin/mysql_upgrade --force -uroot -p
You may use option --upgrade-system-tables that will do the same and if you don't want to touch data tables.
Check that there is no error at this point. If there is no error, then the database is repaired and you should not see this message :  
InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
If you can, restart MySQL and make sure that there is no messages in the error log.