一次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: userCreate 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: userCreate 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)