39

I'm getting the following error in mysql 5.6 community edition:

: Error: 
Table "mysql"."innodb_table_stats"
 not found. InnoDB: Recalculation 
of persistent statistics requested for table "mydatabase"."mytable" 
but the required persistent statistics storage is not present or is corrupted. 
Using transient stats instead.

how do I fix this error?

Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
user121196
  • 30,032
  • 57
  • 148
  • 198

6 Answers6

52

This solved my problem.

/* 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';
Mad Dog Tannen
  • 7,129
  • 5
  • 31
  • 55
  • Can you give a link to a bug report on MySQL website? – Jocelyn May 03 '13 at 15:49
  • On that page theres 2 links, hope it helps! – Mad Dog Tannen May 06 '13 at 08:16
  • 1
    this solved a problem for me when trying to run innobackupex where some tables seem to conflict with those 5 system tables and wouldn't be backed up properly. I didn't notice the incomplete backup at first and when I used it to populate a slave server, then I'd get a "table doesn't exist" due to .ibd files being missing for those tables that conflicted. thanks! – Jason Jan 21 '14 at 00:58
  • I can't get this to work, arg. How do you drop the tables after removing the .frm and .ibd files? It just gives me "no such table" errors. – Kevin Jan 21 '14 at 21:39
  • 1
    Great, thanks. I DROPed them from **mysql** database and imported via adminer the file. Before import I also removed `.ibd` files `root@mikembp:/usr/local/mysql/data/mysql# rm slave_*` `root@mikembp:/usr/local/mysql/data/mysql# rm innodb_*` – michalzuber Mar 04 '14 at 17:35
  • 1
    This helped me too. Just a note, the file should be imported on `mysql` table – piotr_cz Oct 17 '14 at 08:30
  • 1
    That link immediately starts a download, which I'm not a fan of. Also, if the link ever stops working, your answer will become useless. It's better to write the solution and then provide a link at the end. – Parrotmaster Dec 06 '16 at 10:44
  • @Parrotmaster Youre right, ive updated the answer to display the code on site. – Mad Dog Tannen Dec 06 '16 at 12:22
  • Instead of running the given SQL script I ran `mysql_upgrade` to recreate the tables after I had dropped them and deleted the *.ibd files manually. – Marcel Pfeiffer Feb 04 '19 at 09:54
  • This helped me too, I had same problem in window 10 64bit this solution works but after deleting all .frm & .ibd first you have to restart mysql server then execute queries, its works thanks – Priyesh Nov 03 '19 at 16:28
  • worked for me (just needed the innodb_index_stats and innodb_table_stats delete and create. Followed these similar steps https://programmer.help/blogs/innodb-error-table-mysql-innodb_table_stats-not-found.html – jonincanada Jan 21 '20 at 14:00
9

I ran into this issue on a production server recently and the most common answers I found involving dropping tables, deleting files etc. made me feel a little uneasy!

So I thought it might be useful to point out for people reading this in future that in my case the actual cause of the error was my root partition filling up. I had been making a mysql dump at the time and saved it to the wrong place. So the solution was just to free up some disk space and restart the service.

It might be an idea to check that this is not the case for you before moving onto the standard solutions which involve messing around with the structure of MYSQL and have the potential to be a little riskier.

Good Luck,

Mark
  • 141
  • 1
  • 5
  • 1
    If you are using a linux server you can perform this check from the command like with **df -H** the output when using the -H option will be in a more familiar format giving MB GB etc. You will want to look at Use% column in the output if you are nearing 100% this will be you solution. if not the problem lies elsewhere. As I am near 7% use this isnt for me but I wanted to share here to help those who don't have a GUI to work with. – CodingInTheUK Sep 18 '19 at 00:04
8

Read this mysql doc to learn more about Persistent Statistics.

To disable this feature, add innodb_stats_persistent = 0 to my.cfg and restart your mysql server.

3

same issue but crash table 'gtid_slave_pos', Create gtid_slave_pos 

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';
Thoman
  • 742
  • 2
  • 9
  • 20
2

This turned out to be a permission problem for me. Since this was on a development machine where permissions are not locked down, I was able to fix it easily enough:

sudo chmod -R 775 /usr/local/mysql/data

...if that solves it for you, then great. But if the problem is on a production machine, you may wish to investigate permissions a little more thoroughly than my solution.

maybe you would like to try this instead:

sudo chown mysql.mysql -R /usr/local/mysql/data
diov
  • 11
  • 3
glowkeeper
  • 209
  • 2
  • 11
  • 755 is okay, but on my system 700 for directories and 660 for the files are used, I presume to make the security tighter. Of course we are several years on but I wanted to make sure you understood that applying this might actually weaken your security. You can check the permissions really easily with the following: **stat -c '%a %n' /path/to/mysql/*** Its important that you use the asterisk as thats how you get a list and not just the folder. This will give your permissions with octal values. Under ubuntu 18.04 I used: **stat -c '%a %n' /var/lib/mysql/*** – CodingInTheUK Sep 18 '19 at 00:18
  • 1
    @Chris - I reckon if you read my answer more closely, you might see that I understand the implications of permissions entirely. – glowkeeper Sep 29 '19 at 08:58
  • Sorry, my comment was not clear in my intent. I was directing the comment toward somebody reading that may not fully understand it and may fire off the command without looking any further. My comment was with the best of intentions as somebody who made that exact mistake shortly after my Linux journey started. – CodingInTheUK Sep 29 '19 at 09:29
0

If you're running Debian Linux:

My solution to this problem was after dropping the mysql.innodb_table_stats file and removing the associated .frm and .ibd files, I ran this:

dpkg-reconfigure mariadb-server-10.1

It recreates the innodb_* tables for you but for whatever reason had trouble doing it with the files already there (from a previous install).

I consider this safer as the columns and their names may have changed since the answer above was written.

micah94
  • 414
  • 4
  • 9