Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL 5.7 phpmyadmin failed access for root user only

Discussion in 'Database Discussion' started by martin MHC, Nov 5, 2018.

  1. martin MHC

    martin MHC Well-Known Member

    Joined:
    Sep 14, 2016
    Messages:
    151
    Likes Received:
    24
    Trophy Points:
    18
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    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 &
     
    #1 martin MHC, Nov 5, 2018
    Last edited: Nov 5, 2018
  2. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,502
    Likes Received:
    509
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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?
    Code:
    mysql -u root -p <password>
    Can you also confirm whether or not there is a password stored at
    Code:
    /root/.my.cnf
    and if it matches the one you've changed to
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. martin MHC

    martin MHC Well-Known Member

    Joined:
    Sep 14, 2016
    Messages:
    151
    Likes Received:
    24
    Trophy Points:
    18
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    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 likes this.
  4. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,502
    Likes Received:
    509
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @martin MHC


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

    Code:
     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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #4 cPanelLauren, Nov 7, 2018
    Last edited: Nov 7, 2018
  5. martin MHC

    martin MHC Well-Known Member

    Joined:
    Sep 14, 2016
    Messages:
    151
    Likes Received:
    24
    Trophy Points:
    18
    Location:
    UK
    cPanel Access Level:
    Root Administrator
    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 ).
     
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice