mysql database users with last version of ispconfig

Discussion in 'ISPConfig 3 Priority Support' started by tritema, Dec 28, 2016.

  1. tritema

    tritema New Member HowtoForge Supporter

    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
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Please use the debug mode to see what happens in detail.
     
  3. tritema

    tritema New Member HowtoForge Supporter

    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.
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    The above patch should work on MySQl 5.5 and 5.7 servers. It just avoids the use of the set password function completely and changes the data with a "normal" mysql update command directly in the mysql.user database, which is probably not "nice" but at least it works for all MySQL 5.x versions.
     
  5. tritema

    tritema New Member HowtoForge Supporter

    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: [email protected]
    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: [email protected]
    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.
    
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    What you can try is this, edit the file /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php and change line 48 to:

    public $show_error_messages = true;

    this should show MySQL errors then in the debug output.
     
  7. tritema

    tritema New Member HowtoForge Supporter

    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.
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Ok, I'll add a new bugreport for that. I know that it worked here for me at that time, but maybe my database scheme was different. I'll have to test this on a clean new setup again.
     
  9. tritema

    tritema New Member HowtoForge Supporter

    ok thank's
     
  10. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    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'])."');");
    
     

Share This Page