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

 

[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.

 

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

 

[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.

 

[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'

 

[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.

 

[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.

 

[behinam]

hello
i have the same problem, any updates?
thanks

 

[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.

 

[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.

 

[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.