解决MYSQL升级导致MYSQL.COLUMN_STATS报错的问题

2024年12月13日 38

学习笔记

今晚接到同事通知，NextCloud的MySQL数据库刷错误日志，导致空间暴涨，需要检查处理。登录数据库查看日之后，发现日志一直在刷以下错误日志，虽然不影响应用使用，但是确实会造成日志量暴涨：

2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-12-13 22:15:05 7545889 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

经过分析，出现这个错误的原因通常是因为Nextcloud使用的MySQL数据库在版本升级后，系统表mysql.column_stats的结构没有同步更新，导致Nextcloud期望的列类型与实际数据库中的列类型不匹配。具体来说，错误日志中提到的两个问题分别是：

hist_type列类型不匹配：Nextcloud期望的hist_type列类型是enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB')，而数据库中实际的类型是enum('SINGLE_PREC_HB','DOUBLE_PREC_HB')。这意味着数据库中的hist_type列缺少了JSON_HB这个枚举值。
histogram列类型不匹配：Nextcloud期望的histogram列类型是longblob，而数据库中实际的类型是varbinary(255)。这表示数据库中的histogram列类型与Nextcloud期望的类型不一致。

这种问题常发生在使用大版本号的镜像上，比如我用的镜像是mariadb:10 ，在有小版本升级并重新部署时，会直接更新Mariadb版本，用小版本镜像的就没这个问题。解决问题有两个方案：

在docker-compose脚本里面增加MARIADB_AUTO_UPGRADE=1变量，让他启动时自动更新；
执行docker exec mariadb mysql_upgrade -uroot -p[你的密码]进行手动更新；
其实还可以手动修改表结构，但是这个操作风险太大，咱不推荐。

完成修复后，错误日志不刷了，Done!