MySQL5.7.25 下 報錯提示innodb_table_stats 解決方法

最近在做災備資料從庫， 從庫版本使用的是5.7.25， 主庫版本是5.7.22. 配置完主從同步後，瞄了一眼從庫的錯誤日志裡面，突然蹦出一堆的下面這種：

MySQL generates the warning Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade

根據提示，可以看到是因為 mysql.innodb_table_stats 這張表某個列問題。 那麼對比下新老庫看看吧。

如下：

主庫截圖如下：

mysql> select @@version;

+---------------+

| @@version     |

| 5.7.22-22-log |

1 row in set (0.00 sec)

mysql> use mysql

Database changed

mysql> show create table innodb_index_stats\G

*************************** 1. row ***************************

       Table: innodb_index_stats

Create Table: 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

從庫截圖如下：

| 5.7.25-28-log |

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

  `table_name` varchar(199) COLLATE utf8_bin NOT NULL,

可以看出，5.7.25 版本上的 innodb_index_stats 的 `table_name` 列，長度從64 變成了 199

解決方法，就是 在從庫上執行下 mysql_upgrade ,然後重新開機下mysqld程序即可。

5.7.23的 Release Notes 如下：

    位址：https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html

Bugs Fixed

    Important Change; Partitioning: After creating partitioned InnoDB tables with very long names, the table_name columns in the corresponding entries in the mysql.innodb_index_stats and mysql.innodb_table_stats system tables were truncated. To fix this issue, the length of the table_name column in each of these tables has been increased from 64 to 199 characters. In both cases, this is now the same as the lengths of these columns in MySQL 8.0.