Last release 1.1.4 - SQL users connection problems

  • Dear community;


    A small bug has been introduced in the last release (1.1.4) which can prevent the SQL users to connect to the MySQL server. Indeed, to prevent the sql users created by your customers to connect to the MySQL server from any host, we removed the default SQL users with the wildcard host (<user>@%), and we replaced the other users (<user>@localhost) with the value taken from the DATABASE_USER_HOST parameter which is set in the imscp.conf file. This is an error because the value of this parameter for a local MySQL server is 127.0.0.1, meaning that all SQL users set with <user>@localhost will no longer be able to connect to the MySQL server.


    To fix the problem, you must run the script bellow.


    PHP
    1. <?phprequire_once '/var/www/imscp/gui/library/imscp-lib.php';$sqlUserHost = iMSCP_Registry::get('config')->DATABASE_USER_HOST;if($sqlUserHost == '127.0.0.1') { $sqlUserHost = 'localhost';}$stmt = exec_query('SELECT DISTINCT sqlu_name FROM sql_user');if($stmt->rowCount()) { $sqlUser = $row['sqlu_name']; while($row = $stmt->fetchRow(PDO::FETCH_ASSOC)) { exec_query("UPDATE mysql.user SET Host = ? WHERE User = ?", array($sqlUserHost, $sqlUser)); exec_query("UPDATE mysql.db SET Host = ? WHERE Host = ?", array($sqlUserHost, $sqlUser)); } exec_query('FLUSH PRIVILEGES');}


    If after running that script, the problem persist, you can also try to run the following command:


    Shell-Script
    1. # mysqladmin -u root -p<password> flush-hosts


    Note: If some of your user are connecting from remote host, you must know what do to. In next release, customers will be allowed to set the host from which their SQL users are allowed to connect to the MySQL server (including the wildcard which allow to connect from any host)

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

  • If I correctly understand, it only affect user that has this value in imscp.conf file :
    "DATABASE_USER_HOST = 127.0.0.1"


    Right ?


    As I have "DATABASE_USER_HOST = localhost", I didn't notice any SQL bug on my side, I assume I exempt of that one.



    PS: For the little code, maybe close the <?php no ?

    • Yes, you shouldn't be affected
    • No, it's not a good practice to add the ?> php end tag.

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206