[Solved] MySQL User Operation - ERROR 1396 (HY000): Operation CREATE / DROP USER failed for 'user'@'host'

Error Message:
ERROR 1396 (HY000): Operation CREATE USER failed for 'admin'@'%'

Generic Error Message:
Operation %s failed for %s

Error Scenario:
Operation CREATE USER failed
Operation DROP USER failed

Reason:
The reason for this error is, you are trying to do some user operation but the user does not exist on the MySQL system. Also, for drop user, the user details are stored somewhere in the system, even though, you have already dropped the user from MySQL server.

Resolution:
Revoke all access granted to the user, drop the user, and run FLUSH PRIVILEGE command to remove the caches. Now create/drop/alter the user, it will work.

REVOKE ALL ON *.* FROM 'user'@'host';
DROP USER 'user'@'host';
FLUSH PRIVILEGES;

Grant Tables:
The following tables will help you in identifying the user related informations (as of MySQL 5.7):

mysql.user: User accounts, global privileges, and other non-privilege columns
mysql.db: Database-level privileges
mysql.tables_priv: Table-level privileges
mysql.columns_priv: Column-level privileges
mysql.procs_priv: Stored procedure and function privileges
mysql.proxies_priv: Proxy-user privilege




Related MySQL Bug Reports:

I hope this post will help you, if you faced this error on some other scenarios or if you know, some other workaround / solution for this error, please add on the comment section. It will be helpful for other readers.




5 comments:

  1. good post.. i find it much help full

    ReplyDelete
  2. I can’t believe focusing long enough to research; much less write this kind of article. You’ve outdone yourself with this material without a doubt. It is one of the greatest contents. traffic secrets

    ReplyDelete
  3. Omg I Finally Got Helped  !! I'm so excited right now, I just have to share my testimony on this Forum.. The feeling of being loved takes away so much burden from our shoulders. I had all this but I made a big mistake when I cheated on my wife with another woman  and my wife left me for over 4 months after she found out..  I was lonely, sad and devastated. Luckily I was directed to a very powerful spell caster Dr Emu who helped me cast a spell of reconciliation on our Relationship and he brought back my wife and now she loves me far more than ever.. I'm so happy with life now. Thank you so much Dr Emu, kindly Contact  Dr Emu Today and get any kind of help you want.. Via Email emutemple@gmail.com or Call/WhatsApp +2347012841542

    ReplyDelete