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

You may notice one or several of your MySQL databases spitting out errors complaining an innodb_table_stats table is not found. These errors are located in your mysql_error.log. Basically, these errors likely happened when you updated MySQL versions at some point and it no longer makes this table available. You can quickly fix this error in your MySQL database tables following the steps below.

 

1. First, verify the MySQL database you are seeing the error in. Check this in your mysql_error.log User/Applications/MAMP/logs>

2. Open MySQL Workbench. Highlight the schema (database) the error is happening on in the bottom left.

 

Note: You will see the highlighted schema you have selected further bottom left.

 

 

3. You should see a Query 1 tab at the top of the screen. Let’s verify the innodb_table_stats is actually missing from this database. Copy the below script below into the query and execute it as shown below.

 

Note: This script will look for all InnoDB tables.

 

select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';

 

 

InnoDB tables will be listed below in your MySQL Action Output window if found. As long as you see the innodb_table_stats listed, proceed below.

 

4. Proceed to remove and re-add the table. Run the query below similarly to above.

use mysql; drop table innodb_table_stats;

 

 

5. Next, delete the actual SQL files listed below. The actual locations for these files may vary. If you are using MAMP, path is below.

/Library/Application Support/appsolute/NAMP PRO/db/mysql56/mysql/

 

Delete Files:

innodb_index_stats.frm

innodb_index_stats.ibd

 

6. Finally, re-create the tables by executing the query below.

CREATE TABLE `innodb_table_stats` (
 `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
 `clustered_index_size` bigint(20) unsigned NOT NULL,
 `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

 

7. Restart MySQL.

After completing the last step, verify in your mysql_error.log log file the errors are no longer appearing.

 

 

Additionally, if you are seeing errors in your mysql_error.log for other InnoDB tables, you can use the following scripts the same way to remove those.

 

 

# Log into mysql and remove the tables
use mysql;
drop table innodb_index_stats;
drop table innodb_table_stats;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;

 

# Remove the following files located here
/Library/Application Support/appsolute/NAMP PRO/db/mysql56/mysql/
innodb_index_stats.ibd
innodb_index_stats.frm
innodb_table_stats.ibd
innodb_table_stats.frm
slave_master_info.ibd
slave_master_info.frm
slave_relay_log_info.ibd
slave_relay_log_info.frm
slave_worker_info.ibd
slave_worker_info.frm

 

# Create the new tables
USE mysql
CREATE TABLE `innodb_index_stats` (
 `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `stat_value` bigint(20) unsigned NOT NULL,
 `sample_size` bigint(20) unsigned DEFAULT NULL,
 `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
 PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

USE mysql
CREATE TABLE `innodb_table_stats` (
 `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `n_rows` bigint(20) unsigned NOT NULL,
 `clustered_index_size` bigint(20) unsigned NOT NULL,
 `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
 PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

USE mysql
CREATE TABLE `slave_master_info` (
 `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
 `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
 `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
 `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
 `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
 `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
 `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
 `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
 `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
 `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
 `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
 `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
 `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
 `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
 `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
 `Heartbeat` float NOT NULL,
 `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
 `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
 `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
 `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
 `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
 `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
 `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
 PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

USE mysql
CREATE TABLE `slave_worker_info` (
`Id` int(10) unsigned NOT NULL,
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Relay_log_pos` bigint(20) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_seqno` int(10) unsigned NOT NULL,
`Checkpoint_group_size` int(10) unsigned NOT NULL,
`Checkpoint_group_bitmap` blob NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

USE mysql
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

 

*Remember to restart MySQL after*