Mysql-5.7.27下报错提示innodb_table_stats has length mismatch解决方法

小柒博客 MySQL评论55K7字数 1566阅读5分13秒阅读模式

升级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.

若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如本文“对您有用”,欢迎随意打赏,谢谢!

继续阅读
Wechat
微信扫一扫,加我!
weinxin
微信公众号
微信扫一扫,关注我!
weinxin
MySQL最后更新:2024-1-24
小柒博客
  • 本文由 小柒博客 发表于 2021年4月29日18:20:35
  • 声明:本站所有文章,如无特殊说明或标注,本站文章均为原创。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。转载请务必保留本文链接:https://www.yangxingzhen.com/7879.html
匿名

发表评论

匿名网友
:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

拖动滑块以完成验证