一次mysql 5.5升级到5.6导致的ERROR 1805

前阵子将mysql数据库由5.5.14升级到5.6.36，升级后所有的业务数据都正常。运行了几天后，发现在主库上添加用户失败，错误提示为：ERROR 1805 (HY000): Column count of mysql.user is wrong，提示mysql.user表列的数目不对。还真是个坑。下面是其解决方案。

一、故障现象

mysql> create user 'henry'@'localhost' identified by 'password';
ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 43, found 42. The table is probably corrupted

使用grant all privileges 方式创建用户时收到跟上述一样的错误。提示列的数目不对。

二、排错及解决故障

从上面的描述来看，说列的数目不对，因此我们升级后环境mysql.user表的建表语句
(robin@localhost)[(none)]>show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.5.14-log |
+---------------+------------+
1 row in set (0.01 sec)

(robin@localhost)[(none)]>show create table mysql.user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
............
`plugin` char(64) COLLATE utf8_bin DEFAULT '',
`authentication_string` text COLLATE utf8_bin,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

安装一个新的mysql 5.6.36后查看mysql.user表的建表语句
mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 5.6.36 |
+---------------+--------+
1 row in set (0.01 sec)

mysql> show create table mysql.user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
...........
`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)   ---上行为多出的列，即先前使用主从复制时password_expired列没有
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

--为升级后的mysql 5.6.36手工添加这个password_expired列
mysql> alter table mysql.user add column `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N';
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

--再次创建用户成功
mysql> grant all privileges on *.* to 'henry'@'%' identified by 'henrypwd';
Query OK, 0 rows affected (0.00 sec)