MySQL InnoDB error solved InnoDB:Error:table ' mysql '. ' Innodb_table_stats '

Through elk monitoring found that the program connection MySQL DB failed, by looking at the program log and MySQL error log found in MySQL error

View MySQL Logs

Discover InnoDB:Error:table ' mysql '. ' innodb_table_stats ' does not exist in the InnoDB internal

The reason is obviously that the MySQL library's innodb_table_stats table is corrupted.

First log in to MySQL to see if the table exists? Result: there is.

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                        | 

2. mysql> Show Create   table innodb_index_stats;

4. ERROR 1146 (42S02): Table' mysql.innodb_index_stats ' doesn ' t exist

6. Mysql>

The guess is that the table is corrupted, so I plan to rebuild the table

Ls/data/mysql/data/mysql

Innodb_table_stats
Innodb_index_stats
Slave_master_info
Slave_relay_log_info
Slave_worker_info

The found file exists, so delete it directly and recreate it.

RM-RF:

Innodb_table_stats
Innodb_index_stats
Slave_master_info
Slave_relay_log_info
Slave_worker_info

Check the MySQL default storage engine before creating, if MyISAM, you need to specify the storage engine as InnoDB when you create it.

When you source/usr/local/mysql/share/mysql_system_tables.sql this table, notice whether the default storage engine is InnoDB.

Personal operation, found using the above source and failed to reconstruct the above corrupted table

Try these actions

1, first see if these tables are still present, show tables; result: not present

LS ls/data/mysql/data/mysql/Result: not present

2, try to drop table innodb_table_stats purge again; (delete tables completely)

3, create innodb_table_stats manually, Result: Success

4, Cycle 2,3 operation step, re-create the above damaged table.

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1949232

MySQL InnoDB error solved InnoDB:Error:table ' mysql '. ' Innodb_table_stats '