升级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后日志提示报错:文章源自小柒网-https://www.yangxingzhen.cn/7879.html
最近在做数据库手动升级工作时发现一个错误,老库版本5.7.17,新库版本使用的是5.7.27,迁移配置完后,瞄了一眼库的错误日志里面,突然蹦出一堆的下面这种:文章源自小柒网-https://www.yangxingzhen.cn/7879.html
MySQL generates the warning Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade文章源自小柒网-https://www.yangxingzhen.cn/7879.html
根据提示,可以看到是因为mysql.innodb_table_stats和mysql.innodb_index_stats这张表table_name列问题。文章源自小柒网-https://www.yangxingzhen.cn/7879.html
1)对比下新老版本数据库表结构文章源自小柒网-https://www.yangxingzhen.cn/7879.html
5.7.17版本:文章源自小柒网-https://www.yangxingzhen.cn/7879.html
MySQL [(none)]> desc mysql.innodb_index_stats;文章源自小柒网-https://www.yangxingzhen.cn/7879.html
table_name varchar(64) COLLATE utf8_bin NOT NULL,文章源自小柒网-https://www.yangxingzhen.cn/7879.html
5.7.27版本:文章源自小柒网-https://www.yangxingzhen.cn/7879.html
MySQL [(none)]> desc mysql.innodb_index_stats;文章源自小柒网-https://www.yangxingzhen.cn/7879.html
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.
若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如本文“对您有用”,欢迎随意打赏,谢谢!
![weinxin](https://www.yangxingzhen.com/images/weixin.jpg)
![weinxin](https://www.yangxingzhen.com/images/weixingongzhonghao.png)
评论