MySQL 5.7 phpmyadmin failed access for root user only

[martin MHC]

Hello
A new server has been set up. MySQL was given a new - longer - root password.

Now PHPMyAdmin does not connect to the database and does not load properly.

I have attempted to force reset the password using MySQL :: MySQL 5.6 Reference Manual :: B.5.3.2 How to Reset the Root Password as quoted from cPanelLauren here: Problem after mysql root password change and I have failed to find the .pid file ( and cpanel forum for the same doesn't help; mysql...mysql....No mysqld pid file found )

So I did kill mysql and did add a text file with the SQL to set the new root password, and I have restarted the MySQL system.

- User accounts on Phpmyadmin are working fine. Root account only is not connecting.
- Restarting services makes no difference.
- Changing the password via the MySQL documentation makes no difference (maybe due to possible failure to close the .pid file properly).
- Changing the password via the WHM interface caused this issue.
- WHM 74.0.10
- PHPMyAdmin version 4.7.7
- MySQL version 5.7.24
- PhpMyAdmin works correctly on user databases, this issue only effects root phpmyadmin connections.

I have also viewed the PhpMyAdmin config file but this doesn't provide any further help.

I have read several cpanel forum topics; the one referencing this issue is here: Problem after mysql root password change but as mentioned in this thread, I can't find the .pid file to correctly close down the Mysqld instance.

So:

Where is the pid file located so that I can close down and then reset the MYSQL root password as per the official MySQL documentation, and then enable phpmyadmin to load as the root user?

instruction:

> mysqld --init-file=/var/lib/mysql/newpass --user=root &

 

[cPanelLauren]

Hi @martin MHC

The issues noted in the thread you linked are a part of an internal case which is linked in the thread. Are you seeing any errors in the cPanel error logs or the mysql error logs? Also, can you access mysql with the root user/password?

mysql -u root -p <password>

Can you also confirm whether or not there is a password stored at

/root/.my.cnf

and if it matches the one you've changed to

 

[martin MHC]

Hello,
Sorry I should have put in the original post - yes MySQL connects with the correct password on the CLI.

And yes the .my.cnf file also shows the correct password.

As far as can be established the only issue is that PhpMyAdmin does not use the correct password or doesn't understand the password with non-alpha-numeric characters:

!=£%.

I have found on a few other systems (including on WHM that passwords can "break" when using the sterling key (£) ).

With the password updated to remove the % and £ characters, PhpMyAdmin appears to load correctly every time,

@cPanelLauren this SOLVES this issue (for me).


p.s> PhpMyAdmin appears to be using the password in double quotes "..." rather than single quotes '...' which can effect how the string is interpreted in PHP.

 

[cPanelLauren]

Hi @martin MHC


I believe I'm able to replicate this with the £ symbol only. Is the error you were getting:

 mysqli_connect(): (28000/1045): Access denied for user 'root'@'localhost' (using password: YES)  Undefined index: auth_type

Can you tell me where in the password the % symbol was? I tried to replicate with this but was unable to.

 

[martin MHC]

Hi @martin MHC


I believe I'm able to replicate this with the £ symbol only. Is the error you were getting:

 mysqli_connect(): (28000/1045): Access denied for user 'root'@'localhost' (using password: YES)  Undefined index: auth_type

Can you tell me where in the password the % symbol was? I tried to replicate with this but was unable to.

Hello

Yes that looks like the error. I think the issue is just the £ symbol and the % is ok, in this case. My password was a randomly generated string containing those two characters . I don't have it now as I have updated it.

I suspect the issue is to do with MySQL table collations as the £ symbol is not formed correctly on iso-8859-1, in MySQL and therefore becomes � (or similar ).