InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

Question

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup

I found a possible solution here, but it does not seem official. http://forums.mysql.com/read.php?22,578559,579891#msg-579891

2013年12月06日 21:08:00 7f87b1d26700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013年12月06日 21:08:00 7f87b1d26700 InnoDB: Recalculation of persistent statistics requested for table "drupal"."sessions" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013年12月06日 21:08:07 7f903c09c700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

Any official solutions or 100% fixes?

• Ben Lin: repeat with thread in stackoverflow.com/questions/15767652/… You can download the .sql file to create the table by yourself.
• ProxiBlue: things may have moved on since this question. @2018, updating an old mysql, same issue. Found to run : mysql_upgrade -u root -p --force && systemctl restart mysqld upgrades mysql schema and all dbs, solving this issue

Answer 1

I previously addressed this issue in: Cannot open table mysql/innodb_index_stats

These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually:

innodb_index_stats

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;

innodb_table_stats

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;

slave_master_info

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

slave_relay_log_info

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

slave_worker_info

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

ALTERNATIVE

Another workaround would be to create the script from a MySQL 5.6 instance.

Step 01 : Goto a DB Server running MySQL 5.6, or install MySQL 5.6 on a test machine.

Step 02 : mysqldump these 5 tables to a text file

INNODB_TABLES="innodb_index_stats"
INNODB_TABLES="${INNODB_TABLES} innodb_table_stats"
INNODB_TABLES="${INNODB_TABLES} slave_master_info"
INNODB_TABLES="${INNODB_TABLES} slave_relay_log_info"
INNODB_TABLES="${INNODB_TABLES} slave_worker_info"
mysqldump -uroot mysql ${INNODB_TABLES} > InnoDB_MySQL_Tables.sql

Then, you can run InnoDB_MySQL_Tables.sql on any DB Server running MySQL 5.5 before upgrading.

• 111: Solved my problem on a Linux upgrade from 5.5 - Oracle's 5.6. Had to drop some phantom tables, stop mysql, move the bad ibd files from /var/lib/mysql/mysql, restart mysql, then run Rolando's statements.... and Yay no more startup errors and hopefully no more instability. Rolando you're amazing.
• minni: Thank you for your solution. As with the installation of the mysql-community-server package, a script is included that has basically all needed create statements: cat /usr/share/mysql/mysql_system_tables.sql | mysql -uroot -p mysql
• Christopher Schultz: I had this problem and creating the tables from scratch wouldn't work because they "existed already". Turns out that I was switching from one-big-file to file-per-table for InnoDB and I had deleted my ibdata1 file. MySQL didn't re-create these tables on startup and I had to manually move the files representing the tables out of the MySQL data directory in order to use the CREATE statements above (DROP TABLE did not work).
• RolandoMySQLDBA: @ChristopherSchultz thank you for mentioning this. I mentioned this back in Aug of 2015 (dba.stackexchange.com/questions/111616/…). At least you found out on your own, which is good.
• ash_01: Thanks this solved my problem. btw, i encountered the error "table doesnt exist" above while im restoring the last dump of DB prior doing manual upgrade of mysql which is re installing with higher version.

Answer 2

Rolando's answer worked for me with some additions. I had the same problem, with these 5 tables showing via SHOW TABLES, but SELECT or other operations on the table resulted in table not found.

To resolve the issue, using Rolando's answer, I needed to:

• DROP TABLE <tablename> -- all 5 tables

• In the file system, delete the remaining .ibd files (the .frm files were removed by DROP TABLE)

• I then stopped and started the mysqld instance (don't know if it was needed - made me happy)

• The CREATE TABLE statements Rolando provided then ran without issue.

• superjos: just to mention that even when launching DROP TABLE <tablename> I still got the ERROR 1051 (42S02): Unknown table '...' error message, but at least the .frm file disappeared by then.