mysql database users with last version of ispconfig

We have a master/server install. Since we updated all the system to latest version, we are incurring on some strange happenings. The last one is a mysql user issue:
-if i create a new mysql user and associate to a database, all is ok.
-if i update the password of the user created at above point, it says ok, but the login to mysql with that user is no more possible, and we always get access denied.
to resolve the situation, the only way is to change password at commandline with root user or at phpmyadmin with root user.
what have i to check or fix?
thanks
Massimo

 

After some checking we have found that the problem is related only on the mysql 5.7.crap server in the others (5.5 like) there is no problem.
We have discovered that fix: https://git.ispconfig.org/ispconfig/ispconfig3/commit/c8667c4d9cdcadc5749906ed66d1ee9db61b9d3b

BUT, as soon as in our network some server are 5.5 and others are 5.7, we need to apply that fix ONLY on the server with the 5.7 installed right? Otherwise this will break the change pass on 5.5...right?

ty, L.

 

Yes, after that i have wrote my previous ticket, i was goes into the code of our milestone, and just found that this patch is already in the milestone that we use.
So the problem keep the same as before, if this is not the problem, is not clear why we can't update password on mysql 5.7

I have already open the debug log, and compare them with a server that work, with one that does not work. They are the same.
Not easy discover what could be the problem... :-(

THAT DON'T WORK

Code:

Mysql: 5.7.12
S.O.: Debian 8.2
----
03.01.2017-09:45 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
03.01.2017-09:45 - DEBUG - Found 1 changes, starting update process.
03.01.2017-09:45 - DEBUG - Replicated from master: REPLACE INTO `web_database_user` (`database_user_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_id`,`database_user`,`database_user_prefix`,`database_password`,`database_password_mongo`) VALUES ('XXXXX','1','XXXXX','riud','riud','','0','XXXXX','c761','XXXXX',NULL)
03.01.2017-09:45 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'.
03.01.2017-09:45 - DEBUG - Changing MySQL user password for: XXXXX@localhost
03.01.2017-09:45 - DEBUG - Processed datalog_id 303088
03.01.2017-09:45 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
finished.

THAT WORKS

Code:

Mysql: 5.5.46
S.O.: Debian 8.2
----
03.01.2017-10:12 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
03.01.2017-10:12 - DEBUG - Found 1 changes, starting update process.
03.01.2017-10:12 - DEBUG - Replicated from master: REPLACE INTO `web_database_user` (`database_user_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_id`,`database_user`,`database_user_prefix`,`database_password`,`database_password_mongo`) VALUES ('XXXXX','1','XXXXX','riud','riud','','0','XXXXX','XXXXX','XXXXX',NULL)
03.01.2017-10:12 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'.
03.01.2017-10:12 - DEBUG - Changing MySQL user password for: XXXXX@localhost
03.01.2017-10:12 - DEBUG - Processed datalog_id 303093
03.01.2017-10:12 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
finished.

 

The log display unfortunately didn't make the trick. No others info displayed.
So i was going more deeper into the debug, and checking in details the above logs i have discovered that the method called is "db_user_update" into the class "mysql_clientdb_plugin".
That one do the same set password as the method "process_host_list" previously fixed by you.

So i have tried to put the same fix also here:

Code:

File: /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php
Method: db_user_update

Row 679 - $link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");

Row 680 + $link->query("UPDATE mysql.user SET `Password` = '".$link->escape_string($data['new']['database_password'])."' WHERE `Host` = '".$db_host."' AND `User` = '".$link->escape_string($data['new']['database_user'])."'");

But that one does not work. I suppose because on the mysql.user table of the mysql 5.7.x there is no Password field but only the"authentication_string" field.

I'm not sure, but you think that could be that one the problem?

ty, L.

 

ok thank's

 

you can try to replace the line 679

Code:

$link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = '".$link->escape_string($data['new']['database_password'])."';");

with

Code:

$link->query("SET PASSWORD FOR '".$link->escape_string($data['new']['database_user'])."'@'$db_host' = password=('".$link->escape_string($data['new']['database_password'])."');");