Cannot open table mysql/innodb_index_stats [duplicate]

Question

After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well.

The errors I get in the mysql_error.log are:

2013-08-15 12:44:49 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:49 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."pages" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-08-15 12:44:59 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:59 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."translations" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

I cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists

I tried to fix it by googling the errors and trying to see fixes of other people, but it is still not working. I have been working on this for 2 days now.

Please help me solve it.

Community · Accepted Answer · 2020-06-15 09:05:18Z

PROBLEM

In MySQL 5.6, ibdata1 includes 5 InnoDB in the mysql schema.

mysql> select table_name from information_schema.tables
    -> where table_schema='mysql' and engine='InnoDB';
+----------------------+
| table_name           |
+----------------------+
| innodb_index_stats   |
| innodb_table_stats   |
| slave_master_info    |
| slave_relay_log_info |
| slave_worker_info    |
+----------------------+
5 rows in set (0.00 sec)

mysql>

In MySQL versions before 5.6, if you shutdown mysql, delete ibdata1, and start mysql back up, ibdata1 gets recreated. If you do this with MySQL 5.6, these 5 tables are not recreated. Even if your deleted ibdata1, the following 10 files are still in /var/lib/mysql/mysql:

innodb_index_stats.frm
innodb_index_stats.ibd
innodb_table_stats.frm
innodb_table_stats.ibd
slave_master_info.frm
slave_master_info.ibd
slave_relay_log_info.frm
slave_relay_log_info.ibd
slave_worker_info.frm
slave_worker_info.ibd

I learned this early on. With a newly created or damaged ibdata1, there are no corresponding data dictionary entries for those 5 tables.

In your particular case, I suspect you copied ibdata1 from a Linux server to the Windows server. MySQL/Windows would be expecting the data dictionary to have the DOS Filename Format. Moving that ibdata1 to Windows, MySQL/Windows cannot relate to the Linux filename and path for pointing to the .frm and .ibd files.

SOLUTION

STEP 01 : Install MySQL on another DB Server

STEP 02 : mysqldump only those 5 tables

TABLELIST="innodb_index_stats"
TABLELIST="${TABLELIST} innodb_table_stats"
TABLELIST="${TABLELIST} slave_master_info"
TABLELIST="${TABLELIST} slave_relay_log_info"
TABLELIST="${TABLELIST} slave_worker_info"
mysqldump -uroot -p mysql ${TABLELIST} > mysql_innodb_tables.sql

STEP 03 : Copy mysql_innodb_tables.sql to the DB Server with bad tables.

STEP 04 : Execute mysql_innodb_tables.sql

STEP 05 : Run FLUSH TABLES; (Optional)

Give it a Try !!!

too much moves, just drop files, restart mysql and create tables as suggested in other answers! — Tertium, Commented Dec 2, 2015 at 2:02
@Tertium you are right. I wrote a better way four(4) months later : dba.stackexchange.com/questions/54608/… — RolandoMySQLDBA, Commented Dec 2, 2015 at 2:04

Mostafa Nazari · Accepted Answer · 2014-08-14 09:28:00Z

this bug is reported in MySQL Forums (67179) and the solution is mentioned here. please note that you should do clean-up, before creating tables

/* 
  temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines.
  I did the procedure on a clean installed MySql, and it worked for me, at least it stopped
  lines of innodb errors in the log and the use of transient innodb tables. So, do it at
  your own risk..

  1. drop these tables from mysql:
     innodb_index_stats
     innodb_table_stats
     slave_master_info
     slave_relay_log_info
     slave_worker_info

  2. delete all .frm & .ibd of the tables above.

  3. run this file to recreate the tables above (source five-tables.sql).

  4. restart mysqld.

  Cheers, 
  CNL
*/

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

Just a small note. On Linux make sure to restart mysql after deleting .frm and .ibd files. then creating the new tables works like a charm. — user3376782, Commented Dec 2, 2015 at 9:33

user1433454 · Accepted Answer · 2014-08-22 20:33:46Z

3

After an upgrade you need to run "mysql_upgrade -u root" and this will handle all the system table updates.

answered Aug 22, 2014 at 20:33

user1433454

1411 bronze badge

There was no upgrade mentioned - do you mean they need to do it first?
– András Váczi
Commented Dec 2, 2015 at 9:55
This solved the issue for me even though I had never done any upgrades.
– Brian Minton
Commented Nov 20, 2018 at 17:36
we may need move or remove old innodb_ and gtid_slave_pos files before do mysql_upgrade cd /var/lib/mysql; mv innodb_* gtid_slave_pos.* ~
– Sérgio
Commented Apr 9, 2020 at 3:19

Add a comment |

Tertium · Accepted Answer · 2015-12-02 02:01:15Z

for mariadb10:

CREATE TABLE `gtid_slave_pos` (
  `domain_id` int(10) unsigned NOT NULL,
  `sub_id` bigint(20) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `seq_no` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`domain_id`,`sub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

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;

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;

David Alistair Clough · Accepted Answer · 2014-04-10 13:04:15Z

We hit the same problem on deploying 5.6 as part of our standards we always install from .rpm and relocate the data dir after the first set up . 5.5 and lower we would change our settings in the /etc/my.cnf stop mysql and then tar up the database directories in the default data dir and untar to our new one . As we change all our innodb settings we then got a nice new clean innoddb datafile.

So now before we stop our server we run the following on the mysql database

ALTER TABLE innodb_index_stats engine=MYISAM;
ALTER TABLE innodb_table_stats engine=MYISAM;
ALTER TABLE slave_master_info engine=MYISAM;
ALTER TABLE slave_relay_log_info engine=MYISAM;
ALTER TABLE slave_worker_info engine=MYISAM;

We then move all the data to the new location and restart mysql followed by another set of alters thus

ALTER TABLE innodb_index_stats engine=INNODB;
ALTER TABLE innodb_table_stats engine=INNODB;
ALTER TABLE slave_master_info engine=INNODB;
ALTER TABLE slave_relay_log_info engine=INNODB;
ALTER TABLE slave_worker_info engine=INNODB;

This seems to work fine for us and is quick to do

This is very good answer provided one remembers four(4) things: 1) the 5 InnoDB tables are inside MySQL 5.6 ibdata1 2), the .ibd exist in the mysql schema, 3) MySQL 5.5 has no upgrade path to MySQL 5.6 to make them, 4) your steps will only work with an ibdata1 generated by MySQL 5.6. — RolandoMySQLDBA, Commented Apr 10, 2014 at 15:32

Stack Exchange Network

Cannot open table mysql/innodb_index_stats [duplicate]

5 Answers 5

PROBLEM

SOLUTION

Give it a Try !!!

Not the answer you're looking for? Browse other questions tagged
mysql
innodb
windows
linux
mysql-5.6
or ask your own question.

Linked

Hot Network Questions

Cannot open table mysql/innodb_index_stats [duplicate]

5 Answers 5

PROBLEM

SOLUTION

Give it a Try !!!

Not the answer you're looking for? Browse other questions tagged mysqlinnodbwindowslinuxmysql-5.6 or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
innodb
windows
linux
mysql-5.6
or ask your own question.