Mysql innodb error resolution InnoDB: Error: table `mysql`.`innodb _ table_ stats`
Through ELK monitoring, it was found that the program failed to connect to mysql DB. By looking at the log of the program and the error log of mysql, it was found that error appeared in mysql.
View Mysql Log
Found InnoDB: Error: table `mysql`.`innodb _ table_ stats` does not exist in the InnoDB internal
The obvious reason is that the innodb_table_ stats table of the mysql library is corrupted.
First log in to mysql to see if the table exists? Result: it exists.
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 |
Mysql > show create table innodb_index_stats
ERROR 1146 (42S02): Table'mysql.innodb_index_stats' doesn't exist
Mysql >
Guess the table is broken, so I'm going 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
Found that the 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 default storage engine of mysql before creation. If it is MyiSAM, you need to specify the storage engine as INNODB when creating it.
When using the source / usr/local/mysql/share/mysql_system_tables.sql table, please note whether the default storage engine is innodb.
After personal operation, it was found that using the above source failed to rebuild the above corrupted tables.
Try the following
1. See if these tables still exist, show tables; result: they do not exist
Ls ls / data/mysql/data/mysql/ result: does not exist
2. Try drop table innodb_table_stats purge again; (delete the table completely)
3. Create innodb_table_stats manually. Result: successful.
4, loop 2p3 procedure to recreate the above corrupted table.
