MySQL5.7.25 下 报错提示innodb_table_stats 解决方法
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
1 row in set (0.00 sec)从库截图如下：
mysql> select @@version;
+---------------+
| @@version |
+---------------+
| 5.7.25-28-log |
+---------------+
1 row in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
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(199) 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
1 row in set (0.00 sec)可以看出，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.
When upgrading to this release, use mysql_upgrade to effect these changes in your MySQL installation. In the event that you fail to do this, MySQL generates the warning Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade in the error log.
腾讯云开发者
