Skip to main content

User mysql grant permission error + phpmyadmin blank screen

SupraMario
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.
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, 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, 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.

Comments

10 comments

Date Votes
  • cPanelMichael
    Hello, Check to see if the skip-name-resolve option is enabled in your /etc/my.cnf file with the following command:
    grep skip-name-resolve /etc/my.cnf
    This option is unsupported, and can lead to some of the issues you have described. Thank you.
    0
  • SupraMario
    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 :
    - The system received an error from the "MySQL" database "mysql": ER_NONEXISTING_GRANT (There is no such grant defined for user 'DATABASE_USERNAME' on host 'SERVERHOSTNAME') - /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, 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.
    0
  • SupraMario
    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.
    0
  • SupraMario
    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'
    0
  • SupraMario
    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.
    0
  • cPanelMichael
    Clutching at straws, the support ticket I have open on this, is esclating it to level 2 tech.

    Hi, Could you let us know the ticket number? Thank you.
    0
  • behinam
    hello i have the same problem, any updates? thanks
    0
  • cPanelMichael
    i have the same problem, any updates?

    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:
    grep skip-name-resolve /etc/my.cnf
    Thank you.
    0
  • SupraMario
    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 ---
    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. -
    .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.
    0
  • behinam
    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.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post