Skip to main content

MySQL Password reset Operation ALTER USER failed for user

Eric Delorme
  • Updated

Symptoms

You reset the MySQL user password for a cPanel user but that password doesn't work.

 

Description 

The database user can't be changed because of error "ERROR 1396 (HY000): Operation ALTER USER failed for" and can be seen while changing the password and a tail on the cPanel eror_log like so

tail -0f /usr/local/cpanel/logs/error_log

After executing the tail command change the password and if you see output like the following

Error from MySQL query: Cpanel::Exception::Database::Error/(XID fhhbaz) The system received an error from the “MySQL” database “mysql”: ER_CANNOT_USER (Operation ALTER USER failed for 'cpuser'@'localhost')

The MySQL user needs to be recreated. It's possible at some point a delete SQL command was used and caused this to occur.

 

Workaround

The MySQL user for each host needs to be recreated but use the same password hash.

First, collect present information with

select user,host,authentication_string from user where user='cpuser';

for example

mysql> select user,host,authentication_string from user where user='cpuser';
+----------+------------------------+-------------------------------------------+
| user | host | authentication_string |
+----------+------------------------+-------------------------------------------+
| cpuser | localhost | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| cpuser | 10.2.33.87 | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| cpuser | myserver.domain.tld | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
+----------+------------------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Of course XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX is just a place holder of what the real output would be.

Now let's drop these users and recreate them with the following.

drop user cpuser@'localhost';
drop user cpuser@'10.2.33.87';
drop user cpuser@'myserver.domain.tld';

CREATE USER cpuser@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
CREATE USER cpuser@'10.2.33.87' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
CREATE USER cpuser@'myserver.domain.tld' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

Just be sure the correct IP and hostname are set and the same is done for any additional if there are more than 3.

Afterward, proceed with changing the MySQL password.

Related articles