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.
Comments
0 comments
Article is closed for comments.