升级Mysql-5.7.27时报错提示InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade的解决方法。

Mysql-5.7.17升级至Mysql-5.7.27后日志提示报错：

最近在做数据库手动升级工作时发现一个错误，老库版本5.7.17，新库版本使用的是5.7.27，迁移配置完后，瞄了一眼库的错误日志里面，突然蹦出一堆的下面这种：

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.innodb_index_stats这张表table_name列问题。

1）对比下新老版本数据库表结构

5.7.17版本：

MySQL [(none)]> desc mysql.innodb_index_stats;

table_name varchar(64) COLLATE utf8_bin NOT NULL,

5.7.27版本：

MySQL [(none)]> desc mysql.innodb_index_stats;

table_name varchar(199) COLLATE utf8_bin NOT NULL,

可以看出，5.7.27 版本上的innodb_index_stats和innodb_tables_stats的 table_name列，长度从64 变成了199，而我升级后的5.7.27中table_name还是64，问题找到！

解决办法

1）执行mysql_upgrade -u root -p --force

2）重启Mysql服务

/etc/init.d/mysqld restart

Mysql官方文档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.
继续阅读

历史上的今天

4 月
29

• 2020Linux搭建Docker Registry私有镜像仓库 MySQL最后更新：2024-1-24