InnoDB: Error: Table & quot; mysql & quot;. & quot; innodb_table_stats & quot; not found.

1. the following error is reported during Mysqldump:

2014-05-05 14:12:37 7f004a9a2700 InnoDB: Error: Table "mysql". "innodb_table_stats" not found.

However, show tables indicates that it exists:

But the show create table innodb_index_stats; error is as follows:

mysql>  show create table innodb_index_stats;ERROR 1146 (42S02): Table'mysql.innodb_index_stats' doesn't existmysql>

2. It is estimated that the table is broken. Go to the mysql database directory on the database server to view the data files of these tables:

 

 

 

3. The data file and structure file of the table exist.

Then go to $ MYSQL_HOME/share/mysql_system_tables. SQL and search for the table creation statement.

 

Open mysql_system_tables. SQL, find the SQL, and then execute:

mysql> CREATETABLE IF NOT EXISTS innodb_index_stats (    ->         database_name                   VARCHAR(64) NOT NULL,    ->         table_name                      VARCHAR(64) NOT NULL,    ->         index_name                      VARCHAR(64) NOT NULL,    ->         last_update                     TIMESTAMP NOT NULL NOTNULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->         stat_name                       VARCHAR(64) NOT NULL,    ->         stat_value                      BIGINT UNSIGNED NOT NULL,    ->         sample_size                     BIGINT UNSIGNED,    ->         stat_description                VARCHAR(1024) NOT NULL,    ->         PRIMARY KEY (database_name,table_name, index_name, stat_name)    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8COLLATE=utf8_bin STATS_PERSISTENT=0;ERROR 1146(42S02): Table 'mysql.innodb_index_stats' doesn't existmysql>mysql>

Also, the error Table 'mysql. innodb_index_stats 'doesn' texist is reported. It may be caused by that judgment. Remove the judgment and try again:

mysql> CREATETABLE innodb_index_stats (    ->        database_name                   VARCHAR(64) NOT NULL,    ->         table_name                      VARCHAR(64) NOT NULL,    ->         index_name                      VARCHAR(64) NOT NULL,    ->         last_update                     TIMESTAMP NOT NULL NOTNULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->         stat_name                       VARCHAR(64) NOT NULL,    ->         stat_value                      BIGINT UNSIGNED NOT NULL,    ->         sample_size                     BIGINT UNSIGNED,    ->         stat_description                VARCHAR(1024) NOT NULL,    ->         PRIMARY KEY (database_name,table_name, index_name, stat_name)    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8COLLATE=utf8_bin STATS_PERSISTENT=0;ERROR 1146(42S02): Table 'mysql.innodb_index_stats' doesn't exist

It's weird. If it doesn't exist, we won't allow create. Is it because the original data file does not exist, so this strange error is reported during create?

 

4. Go to the database directory and check whether the file already exists:

[Root @ xxx mysql] # ll/home/data/mysql/innodb *-rw ----. 1 mysql 12982 April 9 22: 24/home/data/mysql/innodb_index_stats.frm-rw-rw ----. 1 mysql 180224 May 6 10:38/home/data/mysql/innodb_index_stats.ibd-rw-rw ----. 1 mysql 8830 April 9 22: 24/home/data/mysql/innodb_table_stats.frm-rw-rw ----. 1 mysql 98304 May 6 10: 38/home/data/mysql/innodb_table_stats.ibd [root @ xxx mysql] #

If it is a myisam table, you can also use the command to fix it, but innnodb, You have to restart the database and try again. After restart, enter the mysql database:

mysql> descinnodb_index_stats;ERROR 1146(42S02): Table 'mysql.innodb_index_stats' doesn't exist

If an error is reported, only the drop operation is required:

mysql> droptable mysql.innodb_index_stats;ERROR 1051(42S02): Unknown table 'mysql.innodb_index_stats'mysql>mysql>

5. The RM operation deletes the old bad data table file.

Drop still reports an error. In the end, it seems that there is only one trick. rm-rf drops the innodb_index_stats file that has been broken and cannot be loaded, and then re-create it.

rm -rf/home/data/mysql/data/mysql/innodb*

Rm and then log on to mysql to execute the create operation.

mysql> usemysql;Database changedmysql>mysql>  CREATE TABLE innodb_index_stats (    ->             database_name                   VARCHAR(64) NOT NULL,    ->             table_name                      VARCHAR(64) NOT NULL,    ->             index_name                      VARCHAR(64) NOT NULL,    ->             last_update                     TIMESTAMP NOT NULL NOTNULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->             stat_name                       VARCHAR(64) NOT NULL,    ->             stat_value                      BIGINT UNSIGNED NOT NULL,    ->             sample_size                     BIGINT UNSIGNED,    ->             stat_description                VARCHAR(1024) NOT NULL,    ->             PRIMARY KEY (database_name,table_name, index_name, stat_name)    ->    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binSTATS_PERSISTENT=0;Query OK, 0 rowsaffected (0.02 sec)

Innodb_index_stats is created successfully. Finally, execute the original mysqldump backup operation. Everything is normal.