前阵子将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';
+---------------+------------+
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表的建表语句
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)
