mysql 启动失败： [ERROR] Incorrect definition of table mysql.proc

Question

环境：centos7
尝试了：mysql_upgrade -u root -p 后，输入密码，命令行记录显示操作 ok，然后执行

systemctl stop mysql
systemctl restart mysql

还是无法启动的提示，重新看了 mysqld.log 记录，2 个 error：

181130 8:21:21 [ERROR] Incorrect definition of table mysql.proc: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERRO

181130 8:21:21 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A

google 的方案基本尝试了，也不凑效，怎么办？

Answer 1

这个应该是不同版本的 mysql.proc 和 mysql.event 表结构不兼容引起的。

没有处理过类似的问题，不顾在网站找到了一篇看似靠谱的解决方案

1 /usr/sbin/mysqld --skip-grant --general-log &
2 然后执行

ALTER TABLE mysql.proc MODIFY sql_mode 
SET (...)
ALTER TABLE mysql.event MODIFY sql_mode 
SET (...)

把 mysql.proc 和 mysql.event 修改到对应 mysql 版本的类型。
3 flush privileges;

https://sysadminnightmare.org...

Answer 2

mysql_upgrade跑过了吗？

Answer 3

是升级过程出了问题, 你可以从一个新版本导出这两个表的结构或手工把缺的字段补上.
不管做什么都需要先上 mysql 服务起来才行. 这个可以通过在配置文件里设置

[mysqld]
innodb_force_recovery = 1

innodb_force_recovery取值1~6, 你可以参考一下官方文档.