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

xiaoxiao2021-02-28  29

前阵子将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)

转载请注明原文地址: https://www.6miu.com/read-1450299.html

最新回复(0)