All we need is an easy explanation of the problem, so here it is.
I’m trying to replicate the SQL required to create a user with privileges, if I use HeidiSQL GUI interface to create a USER and set the privileges it works, then I delete the user from the User manager in HeidiSQL and copy SQL from echoed response area at the bottom of HeidiSQL:
CREATE USER 'simon'@'localhost' IDENTIFIED BY 'password';
Just the above, nothing more, but it fails with a dialog containing:
SQL Error (1396): Operation CREATE USER failed for 'simon'@'localhost'
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You may need to perform a
FLUSH PRIVILEGES; before trying to create an account that was recently removed. This is the result of a very old bug in MySQL server. By flushing the privileges the system will properly “forget” that
[email protected] existed.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂