Mysql重建用户报错解决

一：背景

Mysql新建用户报错
ERROR 4006 (HY000): Operation DROP USER failed for ’ ’ as it is referenced as a definer account in a view.

二：故障表现

2.1查询当前用户

2.2删除用户

2.3尝试delete删除

2.4新建用户报错

2.5根据提示查询全局视图

select * from VIEWs where DEFINER like %rw_clescf%';

二：故障原因

究其原因为创建视图时指定了定义用户，删除用户时检查用户被依赖，无法删除。

三：处理措施

3.1去掉definer关键字重建视图

删除依赖用户的视图

drop view schemas.viewname;
create view …… ;
--替换库名和视图名

3.2新建用户失败

mysql> create user  rw_clescf@'%' identified by 'yourPassWD';
ERROR 1396 (HY000): Operation CREATE USER failed for 'rw_clescf'@'%'
mysql> exit

3.3删除用户

drop user rw_clescf;
flush privileges;

3.4再次新建用户成功

create user a@'%' identified by “YourPassWord”;
grant select,insert,update on yourDBname.* TO a;
flush privileges;