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

/ by 

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. Anbarasan1424 June 2019 at 17:34

    I don’t have any idea about commenting on someone’s blog but it’s the first time I really want to appreciate the hard work of this blog admin for sharing this post with us.
    Spoken English Classes in Chennai
    Best Spoken English Classes in Chennai
    IELTS Coaching in Chennai
    IELTS Coaching Centre in Chennai
    English Speaking Classes in Mumbai
    English Speaking Course in Mumbai
    IELTS Classes in Mumbai
    IELTS Coaching in Mumbai
    Spoken English Class in Anna Nagar
    IELTS Coaching in T Nagar

    ReplyDelete
  2. Anonymous16 October 2019 at 22:51

    good post.. i find it much help full

    ReplyDelete
  3. Radley Co Tad14 December 2019 at 16:01

    Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete
  4. depressioncure4 January 2020 at 16:21

    Hey friend, it is very well written article, thank you for the valuable and useful information you provide in this post. Keep up the good work! FYI, please check these related articles:

    शनि देव की आरती – Shani Dev Ki Aarti,
    Jai Ambe Gauri Aarti – जय अम्बे गौरी आरती,
    Aarti Shri Ramayan Ji Ki – श्री रामायणजी की आरती,
    Shri Ram Aarti – आरती श्री रामचन्द्रजी,
    Shri Hanuman Chalisa – श्री हनुमान चालीसा,
    3 Ganesh Aarti – 3 गणेश आरती,
    Shivaji ki Aarti – Om Jai Shiv Omkara | शिवजी की आरती – ओम जय शिव ओंकारा,

    Thanks
    Ritu Sharma

    ReplyDelete

Subscribe to: Post Comments (Atom)