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!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

User mysql grant permission error + phpmyadmin blank screen

Discussion in 'Database Discussions' started by SupraMario, Dec 11, 2017.

  1. SupraMario

    SupraMario Member

    Joined:
    Mar 28, 2006
    Messages:
    15
    Likes Received:
    2
    Trophy Points:
    153
    We have just recently migrated to a new cpanel server, after a serious hardware issue on the previous one.

    With the setup of the new server we have opted to use a 'remote mysql server' to assist with load etc

    Most functions seem to be working fine, except we have started to notice errors with users when they're trying to grant user -> database permissions within the client cpanel.

    The system errors :
    "User "client_username" could not be added to the database "database_name". The system received an error from the "MySQL" database "mysql": ER_PASSWORD_NO_MATCH (Can't find any matching row in the user table)

    -

    This only seems to be affecting 'some users' but unable to identify who or when until the error happens.

    The user will attempt to create a mysql user - success
    The user will attempt to create a mysql database - success
    But when the user goes to 'grant privileges' to user and database, it errors.

    Then when the user also tries to navigate to PHP My Admin, they get nothing but the phpmyadmin logo and a blank screen.

    I have read to resolve the phpmyadmin issue, you simply have to reset the users password, but doing this has not fixed the situation with the blank phpmyadmin screen, and does not fix the mysql grant permission problem.

    I have noticed errors in the /usr/local/cpanel/logs/error_log with the grant permission error.

    Code:
    warn [mysql] There is no such grant defined for user 'username' on host 'SERVERHOSTNAME'
    [2017-12-11 20:04:40 +1100] warn [mysql] Error encountered while fetching data: There is no such grant defined for user 'armourfu' on host 'SERVERHOSTNAME' at /usr/local/cpanel/Cpanel/DBAdmin.pm line 180.
            Cpanel::DBAdmin::_log_error_and_output_return(Cpanel::Mysql=HASH(0x2731600), Cpanel::LocaleString=ARRAY(0x281d3b0)) called at /usr/local/cpanel/Cpanel/DBAdmin.pm line 203
            Cpanel::DBAdmin::_log_error_and_output(Cpanel::Mysql=HASH(0x2731600), Cpanel::LocaleString=ARRAY(0x281d3b0)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 809
            Cpanel::Mysql::updateprivs(Cpanel::Mysql=HASH(0x2731600), "DATABASENAME") called at /usr/local/cpanel/Cpanel/Mysql.pm line 305
            Cpanel::Mysql::create_db(Cpanel::Mysql=HASH(0x2731600), "DATABASENAME") called at /usr/local/cpanel/Cpanel/AdminBin/DB.pm line 89
            Cpanel::AdminBin::DB::CREATE_DATABASE(bin::admin::Cpanel::mysql=HASH(0x2712d78), "DATABASENAME") called at /usr/local/cpanel/Cpanel/AdminBin/Script/Call.pm line 119
            Cpanel::AdminBin::Script::Call::_dispatch_method(bin::admin::Cpanel::mysql=HASH(0x2712d78)) called at /usr/local/cpanel/Cpanel/AdminBin/Script.pm line 166
            Cpanel::AdminBin::Script::new("bin::admin::Cpanel::mysql", "alarm", 120) called at /usr/local/cpanel/Cpanel/AdminBin/Script.pm line 79
            Cpanel::AdminBin::Script::run("bin::admin::Cpanel::mysql", "alarm", 120) called at bin/admin/Cpanel/mysql.pl line 24
    warn [mysql] Error encountered while fetching data: There is no such grant defined for user 'CLIENTUSERNAME' on host 'SERVERHOSTNAME'
    [2017-12-11 20:04:56 +1100] info [mysql] Creating MySQL virtual user DATABASENAME for user CLIENTUSERNAME
    [2017-12-11 20:07:23 +1100] warn [cpmysql] There is no such grant defined for user 'CLIENTUSER' on host 'SERVERHOSTNAME' at /usr/local/cpanel/Cpanel/Mysql.pm line 1951, <STDIN> line 1.
            Cpanel::Mysql::_password_hashes(Cpanel::Mysql=HASH(0x1c5a690), ARRAY(0x10ebfc8)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 803
            Cpanel::Mysql::updateprivs(Cpanel::Mysql=HASH(0x1c5a690)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 1712
            Cpanel::Mysql::dbcache(Cpanel::Mysql=HASH(0x1c5a690), "") called at bin/admin/Cpanel/cpmysql.pl line 458
    warn [cpmysql] There is no such grant defined for user 'CLIENTUSER' on host 'SERVERHOSTNAME'
    [2017-12-11 20:07:23 +1100] warn [cpmysql] Error encountered while fetching data: There is no such grant defined for user 'armourfu' on host 'SERVERHOSTNAME' at /usr/local/cpanel/Cpanel/DBAdmin.pm line 180, <STDIN> line 1.
            Cpanel::DBAdmin::_log_error_and_output_return(Cpanel::Mysql=HASH(0x1c5a690), Cpanel::LocaleString=ARRAY(0x1e30548)) called at /usr/local/cpanel/Cpanel/DBAdmin.pm line 203
            Cpanel::DBAdmin::_log_error_and_output(Cpanel::Mysql=HASH(0x1c5a690), Cpanel::LocaleString=ARRAY(0x1e30548)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 809

    It's trying / checking against SERVERHOSTNAME which is the cpanel hsot (localhost)
    The only mysql server it should be using is a remote mysql system that has its profile setup and is functioning.

    The issue it seems, SERVERHOSTNAME = the actual cpanel box, not the remote mysql server.
    I have no idea why its doing this for some users but not all.

    I thought it might only affect 'new' accounts created since the migration but its affecting new and old and some others 'just work'.

    I've lodged a support ticket for this but hoping to get a quicker resolution to this problem.
     
    #1 SupraMario, Dec 11, 2017
    Last edited by a moderator: Dec 27, 2017
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,502
    Likes Received:
    1,616
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Check to see if the skip-name-resolve option is enabled in your /etc/my.cnf file with the following command:

    Code:
    grep skip-name-resolve /etc/my.cnf
    This option is unsupported, and can lead to some of the issues you have described.

    Thank you.
     
  3. SupraMario

    SupraMario Member

    Joined:
    Mar 28, 2006
    Messages:
    15
    Likes Received:
    2
    Trophy Points:
    153
    Yep I had that option enabled in the my.cnf, I've removed it and restarted.

    All of the same issues mentioned still exist.

    When trying to assign permissions via the user client (grant user -> DB with all permissions)

    Error Popup :
    Code:
    -
    The system received an error from the “MySQL” database “mysql”: ER_NONEXISTING_GRANT (There is no such grant defined for user &#39;DATABASE_USERNAME&#39; on host &#39;SERVERHOSTNAME&#39;)
    -
    
    /usr/local/cpanel/logs/error
    
    [2017-12-12 07:41:47 +1100] warn [cpmysql] There is no such grant defined for user 'cpanelusername' on host 'SERVERHOSTNAME' at /usr/local/cpanel/Cpanel/Mysql.pm line 1951, <STDIN> line 1.
            Cpanel::Mysql::_password_hashes(Cpanel::Mysql=HASH(0x164d660), ARRAY(0x10ebfc8)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 803
            Cpanel::Mysql::updateprivs(Cpanel::Mysql=HASH(0x164d660)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 1712
            Cpanel::Mysql::dbcache(Cpanel::Mysql=HASH(0x164d660), "") called at bin/admin/Cpanel/cpmysql.pl line 458
    warn [cpmysql] There is no such grant defined for user 'CPANELUSER' on host 'SERVERHOSTNAME'
    
    Where SERVERHOSTNAME is listed, that is the hostname of the server cpanel is running on, its not even trying to connect to the remote mysql server when this action is called.

    It's like there is some setting that cpanel has where its just ignoring the remote mysql profile and trying to do everything on its localhost instead.
     
    #3 SupraMario, Dec 11, 2017
    Last edited by a moderator: Dec 12, 2017
  4. SupraMario

    SupraMario Member

    Joined:
    Mar 28, 2006
    Messages:
    15
    Likes Received:
    2
    Trophy Points:
    153
    Actually I just fixed one error it seems.

    Went into /usr/local/cpanel/etc/cpanel.config

    Noticed there are 'mysql' lines in there that are not present in the TWEAK SETTINGS in the cpanel system (68.0.19)
    Even though these mysql lines are apparently not used anymore , they were set as follows :

    [root@HOST etc]# grep mysql cpanel.config
    mysql-host=localhost
    mysql-version=5.5
    mysqldebug=0
    usemysqloldpass=0

    You are supposed to be able to edit these values from TWEAK SETTINGS but there is no reference to any 'mysql' settings there.
    I then decided to edit the file manually and updated the mysql-host + mysql-version to the correct information as per what my remote mysql setup is

    mysql-host=remote-db1
    mysql-version=5.5.56

    I then restarted cpanel srv as per the header instructions in the cpanel.config
    [root@HOST etc]# /usr/local/cpanel/scripts/restartsrv_cpsrvd
    Waiting for “cpsrvd” to restart gracefully ……waiting for “cpsrvd” to initialize ………finished.

    Service Status
    cpanel (cpsrvd (SSL) - waiting for connections ) is running as root with PID 2133 (systemd+/proc check method).

    Startup Log
    Dec 11 21:18:52 SERVERHOSTNAME crontab[1995]: (root) LIST (root)
    Warning: Journal has been rotated since unit was started. Log output is incomplete or unavailable.

    cpsrvd restarted successfully.

    --

    I then attempted to again grant user permissions to the database via the users client control panel and this time, SUCCESS!
    It granted the permissions

    -
    You saved “CPANELDATABASEUSERNAME”’s privileges on the database “CPANELDATABASE”.
    -

    Still outstanding is the blank PHPMYADMIN though.
     
  5. SupraMario

    SupraMario Member

    Joined:
    Mar 28, 2006
    Messages:
    15
    Likes Received:
    2
    Trophy Points:
    153
    Seems I've resolved the blank PHPMYADMIN issue as well.

    After making the above changes to the cpanel.config file that were mentioned, I once again 'reset' the customers cpanel password to what it was already set too, in the hope that maybe when it updates, it will now update the correct mysql system (instead of presumably just erroring when I attempted this before)

    The password was reset successfully (as it stated before - when the cpanel.config file had the wrong information)

    Note :: this test was done after I made the above changes to the cpanel.config

    I then logged in as the cpanel user, and clicked on PHPMYADMIN and it started up perfectly straight away.

    So it would seem this is actually a bug, cpanel is using the mysql variables stored in cpanel.config that are apparently no longer actually used, and ignoring the remote mysql profile settings for some functions but not all functions.

    Modifying that /usr/local/cpanel/etc/cpanel.config seems to have corrected what has been causing me days of headaches trying to work out 'why'
     
  6. SupraMario

    SupraMario Member

    Joined:
    Mar 28, 2006
    Messages:
    15
    Likes Received:
    2
    Trophy Points:
    153
    Update on this, the problem reoccurred on another account.

    Seems the above updates haven't fixed it in total. What I have noticed is it seems the system when doing anything database related is trying to connect to itself (which is what its default behaviour was on the old server - before migrating to this new one with remote mysql)

    I then had an account where I simply clicked the 'make changes' button 2 or 3 times when attempting to grant permissions and suddenly it worked.

    Almost as if its cached or there are user settings telling the system where to connect, perhaps in a user.yaml file? and if it doesnt work after X attempts, refresh/rebuild the connection data perhaps. Clutching at straws, the support ticket I have open on this, is esclating it to level 2 tech.
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,502
    Likes Received:
    1,616
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hi,

    Could you let us know the ticket number?

    Thank you.
     
  8. behinam

    behinam Member

    Joined:
    May 9, 2017
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Dubay
    cPanel Access Level:
    Root Administrator
    hello
    i have the same problem, any updates?
    thanks
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    41,502
    Likes Received:
    1,616
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Could you elaborate on the specific issue you are facing (there were a few referenced in this thread)? Did you already check to see if skip-name-resolve is enabled in your /etc/my.cnf file? EX:

    Code:
    grep skip-name-resolve /etc/my.cnf
    Thank you.
     
  10. SupraMario

    SupraMario Member

    Joined:
    Mar 28, 2006
    Messages:
    15
    Likes Received:
    2
    Trophy Points:
    153
    Sorry guys, I should of included the updates, there were a number of emails back/forth with support on this..

    The following seemed to resolve the issue :

    first I ran a full backup of mysql on the remote server before william executed the function.
    I also ensured that in my my.cnf I had this -> #skip-name-resolve=1

    ---
    Code:
    Hi,
    
    I believe this is an issue left over from 'skip-name' being enabled and/or hostname changes; which would only affect older users, and not newly created accounts. I would like to restore all grants for all users on the server; however, backups should be taken first. Since you have not provided access to the MySQL server, could you please ensure proper backups are taken first?
    
    I would then like to attempt restoring grants with the script below:
    ====
    # /usr/local/cpanel/bin/restoregrants --help
    /usr/local/cpanel/bin/restoregrants [--cpuser=cpuser] [--db=mysql|pg] [--dbuser=dbuser|--all]
    ====
    
    Thanks,
    
    --
    William L.
    Technical Analyst II
    
    --

    I've gone through the .bash_history of the cpanel ticket number referring to this to show you the exact command that was run.

    -
    Code:
    .bash_history.cpanel_ticket.9101019:for i in /var/cpanel/users/*;do [ "${i##*/}" == system ] || echo /usr/local/cpanel/bin/restoregrants --cpuser="${i##*/}" --db=mysql --all;done
    .bash_history.cpanel_ticket.9101019:for i in /var/cpanel/users/*;do [ "${i##*/}" == system ] || /usr/local/cpanel/bin/restoregrants --cpuser="${i##*/}" --db=mysql --all;done
    
    You can see he executed it first time to just see what would be run before running it properly.

    After this was done it seemed to fix most problems, I did have the same issue pop up 3 times since, but when that happened in each instance, I was able to simply do the "password modification" via WHM for that user and reset their password to what I knew it was and after that, phpMyAdmin / granting permissions would work again.
     
    cPanelMichael likes this.
  11. behinam

    behinam Member

    Joined:
    May 9, 2017
    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Dubay
    cPanel Access Level:
    Root Administrator
    Hello
    i commented out the line "skip-name-resolve" in my.cnf and problem is fixed for the new created users.
    and i will run the following script:
    /usr/local/cpanel/bin/restoregrants --all
    to fix the issue for older users.
    thank you.
     
Loading...

Share This Page