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

小柒助手 2021年5月7日17:41:30Mysql评论15.7K字数 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的解决方法。文章源自小柒网-https://www.i7ti.cn/1204.html

Mysql-5.7.17升级至Mysql-5.7.27后日志提示报错:文章源自小柒网-https://www.i7ti.cn/1204.html

最近在做数据库手动升级工作时发现一个错误,老库版本5.7.17,新库版本使用的是5.7.27,迁移配置完后,瞄了一眼库的错误日志里面,突然蹦出一堆的下面这种:文章源自小柒网-https://www.i7ti.cn/1204.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.i7ti.cn/1204.html

根据提示,可以看到是因为mysql.innodb_table_stats和mysql.innodb_index_stats这张表table_name列问题。文章源自小柒网-https://www.i7ti.cn/1204.html

1)对比下新老版本数据库表结构文章源自小柒网-https://www.i7ti.cn/1204.html

5.7.17版本:文章源自小柒网-https://www.i7ti.cn/1204.html

MySQL [(none)]> desc mysql.innodb_index_stats;文章源自小柒网-https://www.i7ti.cn/1204.html

table_name varchar(64) COLLATE utf8_bin NOT NULL,文章源自小柒网-https://www.i7ti.cn/1204.html

5.7.27版本:文章源自小柒网-https://www.i7ti.cn/1204.html

MySQL [(none)]> desc mysql.innodb_index_stats;文章源自小柒网-https://www.i7ti.cn/1204.html

table_name varchar(199) COLLATE utf8_bin NOT NULL,文章源自小柒网-https://www.i7ti.cn/1204.html

可以看出,5.7.27 版本上的innodb_index_stats和innodb_tables_stats的 table_name列,长度从64 变成了199,而我升级后的5.7.27中table_name还是64,问题找到!文章源自小柒网-https://www.i7ti.cn/1204.html

解决办法文章源自小柒网-https://www.i7ti.cn/1204.html

1)执行mysql_upgrade -u root -p --force文章源自小柒网-https://www.i7ti.cn/1204.html

2)重启Mysql服务文章源自小柒网-https://www.i7ti.cn/1204.html

/etc/init.d/mysqld restart文章源自小柒网-https://www.i7ti.cn/1204.html

Mysql官方文档5.7.23的Release Notes如下:文章源自小柒网-https://www.i7ti.cn/1204.html

地址:https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-23.html文章源自小柒网-https://www.i7ti.cn/1204.html

相关内容:文章源自小柒网-https://www.i7ti.cn/1204.html

Bugs Fixed文章源自小柒网-https://www.i7ti.cn/1204.html

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.文章源自小柒网-https://www.i7ti.cn/1204.html

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.文章源自小柒网-https://www.i7ti.cn/1204.html

继续阅读
若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如果喜欢,请打赏支持本站,谢谢大家!
  • 微信号
  • 微信扫一扫加我!
  • weinxin
  • 微信公众号
  • 微信扫一扫关注我!
  • weinxin
广告也精彩
小柒助手
  • 本文由 发表于 2021年5月7日17:41:30
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
匿名

发表评论

匿名网友 填写信息

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

确定