MySQL Error - Cannot open table mysql.innodb_index_stats

Just got this error while adding a new MySQL instance on my DEV server. Cannot open table mysql.innodb_index_stats Inside the error log: InnoDB: Error: Table "mysql"."innodb_table_stats" not found

  • I have no idea what started this but after doing some research i ended up finding that is bug and there is a solution for it.
Here are the steps i have followed and solved my problem: 1- You need to drop these tables from mysql schema:
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;
<span class="com">2- Delete all .frm &amp; .ibd of the tables above from the operation system.

</span>
<span class="com">3- Run this SQL to recreate the tables.
</span>
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;

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;

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';

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';

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';
<span class="com">
4 - Restart your MySQL instance and keep an eye on you error log.

</span><span class="com">I  hope this was useful</span> 
<span class="com"> </span>