How to resolve MySQL error "ERROR 1396 (HY000): Operation ALTER USER failed for 'myuser'@'localhost'?"

Question

I'm trying to change the password of one of the users on a MySQL database.

When I do :

ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

I get :

ERROR 1396 (HY000): Operation ALTER USER failed for 'myuser'@'localhost'

Here are some more details :

• I'm using the root account to do this.
• Also, I can see the name of myuser when doing this : SELECT User, Host FROM mysql.user;
• Also please note that the user I'm trying to change the password for might be in use, as it is a very common user.

Can anyone explain me why I have this error ?

If the user being already logged was a reason why I get the error, what would be the solution ?

Here is my version of MySQL : mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper

Alexandre

Answer 1

your use 'myuser'@'localhost'

You should check with

select * from mysql.user;

Which hosts the user has access to.

If the host is %

ALTER USER 'myuser'@'%' IDENTIFIED BY 'mypassword';

Answer 2

In my case, I had tried to manually change the authentication_string data. After that I am neither able to change password, nor the end user is able to login by the new password I provided. I used SHA1(UNHEX(SHA1("password"))) algorithm to manually update the password and it turned out that my MySQL version uses a different algorithm.

At this point you need to determine the exact algorithm used by your version of MySQL and then manually update the authentication_string. Else it is now almost impossible to change password using ALTER and IDENTIFIED BY statement.

Only resolution for my case was to DROP the user and again create the user again.