Fix: ERROR 1396 (HY000): Operation CREATE USER failed in MySQL?

  Fix: ERROR 1396 (HY000): Operation CREATE USER failed in MySQL?


This is a mysql 8 bug.

Suppose the user is myuser.

1. The user already exists. Delete them with DROP. (not DELETE). 

    DROP USER myuser.

    DO NOT use "delete from mysql.user where user=myuser".

2. You already deleted them with DELETE. Try it again with DROP, even if they do not exist.

    If you are too late and you already made the mistake in (1) above you can either create a new user with a different name, e.g. myuser2, or, repeat the drop statement above in (1).

3. The password doesn't meet the default MySQL 8 requirements.

    Sometimes it is because the password isn't good enough. It must be 8 chars minimum, with at least one caps, one lower, one number, and one punctuation.

4. Check your mysql supports old passwords (mysql_native_password).

   Use CREATE USER 'myuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 's0m3Passw0rd!@#';


Comments

Post a Comment

Popular posts from this blog

what to do if you crashed your database OR it says there are no tables (but there are!)

Sometimes moving a database file ( /var/lib/mysql/* ) breaks the records inside mysql's brain about what databases it had and what their tables were. If you did not use mysqldump to take backups, you are in trouble. Unfortunately if you do this, you have a long night ahead of you. However, help is at hand. A symptom of breaking Moodle in this way is you get this error:     Config table does not contain version, can not continue, sorry.     which means it cannot read the table mdl_config. The basic steps are: 1. Move your crashed/confused databases out the way so you don't break them further.  2. Recreate your mysql instance. 3. Recreate the databases and tables WITHOUT their contents (just the database names and table structures). 4. Tell mysql to forget their contents without using drop command (you use discard instead) 5. move your original db files back into place 6. Tell mysql to re-scan (import) the db files. 7. Create a timed script to run backups Here are the detailed st
Read more

Permanently save login password for mysql client

 If you find it a nuisance to constantly type the mysql password while working with mysql, you can tell it to save, and thereafter it won't ask for it. WARNING. This exposes your database to hackers in that if they gain access to your account, your copy of mysql is also vulneralble. The command is: mysql_config_editor set --host=localhost --user=<whoever> --password
Read more