ISPConfig 3 database user cannot login to phpmyadmin

Discussion in 'Installation/Configuration' started by raymondnexsoftech, Sep 14, 2017.

  1. raymondnexsoftech

    raymondnexsoftech New Member

    I followed to the tutorial "The Perfect Server - Ubuntu 16.04 (Xenial Xerus) with Apache, PHP, MySQL, PureFTPD, BIND, Postfix, Dovecot and ISPConfig 3.1" to install the server, and all the things are fine, except the phpmyadmin always return the following when using database user login:
    #1045 - Access denied for user 'xxx'@'localhost' (using password: YES)
    I tried to reinstall the whole server few times and none of the try works.

    Does anyone have idea?

    Thanks
     
    Last edited: Sep 14, 2017
  2. HSorgYves

    HSorgYves Member HowtoForge Supporter

    Did you change the plugin for the root user?
    echo "update mysql.user set plugin = 'mysql_native_password' where user='root';" | mysql -u root
     
  3. till

    till Super Moderator Staff Member ISPConfig Developer

    And you created a database in ISPConfig and not just a database user? Database users get added when you add the database.
     
  4. raymondnexsoftech

    raymondnexsoftech New Member

    Thank for both of your reply.
    Let me explain more about my situation:
    I can login to phpmyadmin remotely using account "root" (which I don't know how to disable it since it will have security issue). So I can look into the database
    I found that :
    1) No database user created in mysql.user (I guess this should be normal)
    2) The database user were found in dbispconfig.web_database_user
    3) The data in file "/usr/local/ispconfig/server/lib/mysql_clientdb.conf" (where "xxx" is the correct password for root):
    PHP:
    <?php

    $clientdb_host                  
    'localhost';
    $clientdb_user                  'root';
    $clientdb_password              'xxx';

    ?>
    HSorgYves: Tried your solution, but no luck. The tutorial was suggested to set the plugin to "". I also tried but also no work
    till: I created database user, then created database and associate the user to that database.
     
  5. till

    till Super Moderator Staff Member ISPConfig Developer

    ok, fine.so in this case, you should see the database and the database user when logging into mysql with phpmyadmin as root user. You can use the ispconfig debug mode https://www.faqforge.com/linux/debugging-ispconfig-3-server-actions-in-case-of-a-failure/ when you add a new database which uses the same mysql user to see if it throws any errors.
     
  6. raymondnexsoftech

    raymondnexsoftech New Member

    Tried and it return the following warnings:

    Code:
    PHP Warning:  Invalid argument supplied for foreach() in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 86
    Here is the code related to:
    PHP:
            if($result) {
                while (
    $row $result->fetch_assoc()) {
                    
    $sql_plugins[] = $row['plugin_name'];
                }
                
    $result->free();
                foreach (
    $sql_plugins as $plugin$app->log("MySQL-Plugin $plugin enabled - can not execute function process_host_list"LOGLEVEL_ERROR);        // <= this line
                
    return false;
            }
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    Please post the complete debug output that you got on the shell when you ran the server.sh command.
     
  8. raymondnexsoftech

    raymondnexsoftech New Member

    Actually this is the only log that I got
    Screen Shot 2017-09-16 at 1.29.08 AM.png
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    You missed enabling the debug mode. Please see the link that I posted for instructions.
     
  10. raymondnexsoftech

    raymondnexsoftech New Member

    Sorry I missed this part. And here is the output:

    Delete existing database:
    Code:
    16.09.2017-03:47 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    16.09.2017-03:47 - DEBUG - Found 1 changes, starting update process.
    16.09.2017-03:47 - DEBUG - Calling function 'db_delete' from plugin 'mysql_clientdb_plugin' raised by event 'database_delete'.
    PHP Warning:  Invalid argument supplied for foreach() in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 86
    16.09.2017-03:47 - DEBUG - Dropping MySQL database: c1DV_db
    16.09.2017-03:47 - DEBUG - Processed datalog_id 67
    16.09.2017-03:47 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.
    Then delete existing database user:
    Code:
    16.09.2017-03:50 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    16.09.2017-03:50 - DEBUG - Found 2 changes, starting update process.
    16.09.2017-03:50 - DEBUG - Calling function 'db_user_delete' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_delete'.
    16.09.2017-03:50 - DEBUG - Processed datalog_id 71
    16.09.2017-03:50 - DEBUG - Calling function 'db_user_delete' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_delete'.
    16.09.2017-03:50 - DEBUG - Processed datalog_id 72
    16.09.2017-03:50 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.
    Then I create database user:
    Code:
    16.09.2017-03:51 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    16.09.2017-03:51 - DEBUG - Found 1 changes, starting update process.
    16.09.2017-03:51 - DEBUG - Calling function 'db_user_insert' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_insert'.
    16.09.2017-03:51 - DEBUG - Processed datalog_id 73
    16.09.2017-03:51 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.
    And finally, create database:
    Code:
    16.09.2017-03:53 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    16.09.2017-03:53 - DEBUG - Found 2 changes, starting update process.
    16.09.2017-03:53 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'.
    16.09.2017-03:53 - DEBUG - Processed datalog_id 74
    16.09.2017-03:53 - DEBUG - Calling function 'db_insert' from plugin 'mysql_clientdb_plugin' raised by event 'database_insert'.
    16.09.2017-03:53 - DEBUG - Created MySQL database: c1DV_db
    PHP Warning:  Invalid argument supplied for foreach() in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 86
    16.09.2017-03:53 - DEBUG - Processed datalog_id 75
    16.09.2017-03:53 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.
     
  11. Thomas CARTER

    Thomas CARTER New Member

    Hi I have the same problem, and following Til's suggestions get the same warning :
    PHP Warning: Invalid argument supplied for foreach() in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 86

    I already have 30+ IspConfig installations. I did a fresh install Ubuntu 16.04 on a Proxmox 4, LXC Container then directly Perfect server script, added my first client and site, setting up an FTP, Database user and Database and when I try phpmyadmin I too get
    #1045 - Access denied for user 'xxx'@'localhost' (using password: YES)
    I did this once Wednesday morning (13 sept 2017), then this morning (13 sept 2017) removed the container reinstalled everything and had the same problem.
    Only difference with 2 installs was I tried to leave root login the second time with :
    Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
    Disallow root login remotely? [Y/n] n

    but I can't log into phpmyadmin with root either.

    Server OVH with Proxmox Virtual Environment 4.3-3/557191d3
    #cat /etc/lsb-release
    DISTRIB_ID=Ubuntu
    DISTRIB_RELEASE=16.04
    DISTRIB_CODENAME=xenial
    DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"

    Script : https://www.howtoforge.com/tutorial...l-pureftpd-bind-postfix-doveot-and-ispconfig/

    It did create the database (called c1_1) :
    MariaDB [(none)]> show databases;
    +--------------------+
    | Database |
    +--------------------+
    | c1_1 |
    | dbispconfig |
    | information_schema |
    | mysql |
    | performance_schema |
    | phpmyadmin |
    | roundcube |
    +--------------------+
    7 rows in set (0.00 sec)

    But A can't login using the c1_1 user and password :
    [email protected]:/# mysql -u c1_1 -p
    Enter password:
    ERROR 1045 (28000): Access denied for user 'c1_1'@'localhost' (using password: YES)
    As root I can run mysql without a password
    # mysql
    And with a password :
    # mysql -u root -p
    Enter password:
    Welcome to the MariaDB monitor. Commands end with ; or \g.
    Your MariaDB connection id is 1636
    Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

    Like raymondnexsoftech above, the data in file "/usr/local/ispconfig/server/lib/mysql_clientdb.conf" (where "xxx" is the correct password for root):

    <?php
    $clientdb_host = 'localhost';
    $clientdb_user = 'root';
    $clientdb_password = 'xxx';
    ?>

    I followed Til's instructions for debuging, creating a new user : c1_2 :

    # /usr/local/ispconfig/server/server.sh
    16.09.2017-11:27 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    16.09.2017-11:27 - DEBUG - Found 1 changes, starting update process.
    16.09.2017-11:27 - DEBUG - Calling function 'db_user_insert' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_insert'.
    16.09.2017-11:27 - DEBUG - Processed datalog_id 14
    16.09.2017-11:27 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.

    Then created a new database c1-2 with user c1_2 :

    # /usr/local/ispconfig/server/server.sh


    16.09.2017-11:31 - DEBUG - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
    16.09.2017-11:31 - DEBUG - Found 2 changes, starting update process.
    16.09.2017-11:31 - DEBUG - Calling function 'db_user_update' from plugin 'mysql_clientdb_plugin' raised by event 'database_user_update'.
    16.09.2017-11:31 - DEBUG - Processed datalog_id 15
    16.09.2017-11:31 - DEBUG - Calling function 'db_insert' from plugin 'mysql_clientdb_plugin' raised by event 'database_insert'.
    16.09.2017-11:31 - DEBUG - Created MySQL database: c1_2
    PHP Warning: Invalid argument supplied for foreach() in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 86
    16.09.2017-11:31 - DEBUG - Processed datalog_id 16
    16.09.2017-11:31 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.

    Any help would be appreciated. And as always, many thanks to the ISPConfig team for an excellent product.

    Thomas CARTER, Cergy Internet, France
     
  12. HSorgYves

    HSorgYves Member HowtoForge Supporter

    What is the output of:
    SELECT * FROM `mysql`.`user` WHERE LEFT(`mysql`.`user`,2) = 'c1'
     
  13. till

    till Super Moderator Staff Member ISPConfig Developer

    And please check if you have a password validation plugin enabled:

    https://git.ispconfig.org/ispconfig/ispconfig3/issues/4777

    Did you disable the password plugin for the root user? latest Ubuntu and debian version allow root logins from mysql shell only and not from localhost.
     
  14. raymondnexsoftech

    raymondnexsoftech New Member

    You reminded me one thing.
    First time when I installed the server (it was about 9 Sept), I did not encounter this problem. But due to some reasons, I need to reinstall the server and the problem occurred.
    In my first installation, since there are lots of unexpected situation comes out (such as cannot run freshclam), I tried out lots of solutions from Internet. I'm not sure if these processes fixed the problem.
     
  15. Thomas CARTER

    Thomas CARTER New Member

    MariaDB [mysql]> SELECT * FROM mysql.user WHERE LEFT(user,2) = 'c1';
    Empty set (0.00 sec)

    and you're right, it's not creating the user :
    MariaDB [(none)]> use mysql;
    MariaDB [mysql]> SELECT Host, User FROM user ;
    +-----------+------------+
    | Host | User |
    +-----------+------------+
    | localhost | ispconfig |
    | localhost | phpmyadmin |
    | localhost | root |
    | localhost | roundcube |
    +-----------+------------+
    4 rows in set (0.00 sec)
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    Which ISPConfig version do you have installed, 3.1.6 or 3.1dev? Maybe there is an issue in the dev (stable-3.1) branch at the moment that we don't know yet, thats's why I ask.
     
  17. Thomas CARTER

    Thomas CARTER New Member

    MariaDB [(none)]> uninstall plugin validate_password;
    ERROR 1305 (42000): PLUGIN validate_password does not exist

    but thanks anyway.
     
  18. Thomas CARTER

    Thomas CARTER New Member


    Apparently I have 3.1dev.

    In the installation step 17 I did :
    cd /tmp
    wget -O ispconfig.tar.gz https://git.ispconfig.org/ispconfig/ispconfig3/repository/archive.tar.gz?ref=stable-3.1
    tar xfz ispconfig.tar.gz
    cd ispconfig3*/install/

    How should I correct this to install 3.1.6, and can I just write over the old one or is it better to reinstall the container from scratch?



    Server: mut104.blyenburgh.com (Ubuntu 16.04.3 LTS (Xenial Xerus)) ISPConfig 3.1dev

    State: info (0 unknown, 0 info, 1 warning, 0 critical, 0 error)

    Ok
    The state of your Hard-Disk space is ok [More...]
    Your virus protection is ok [More...]
    Your Mail queue load is ok [More...]
    Your RAID is ok [More...]
    Your Server load is ok [More...]
    All needed services are online [More...]
    The System Log is O.K. [More...]

    Warning
    One or more components needs an update [More...]
    Update Status (Server : mut104.blyenburgh.com)
    Inst libllvm4.0 (1:4.0-1ubuntu1~16.04.2 Ubuntu:16.04/xenial-updates, Ubuntu:16.04/xenial-security [amd64])
    Inst libsensors4 (1:3.4.0-2 Ubuntu:16.04/xenial [amd64])
    Inst libgl1-mesa-dri [11.2.0-1ubuntu2] (17.0.7-0ubuntu0.16.04.1 Ubuntu:16.04/xenial-updates [amd64])
    Conf libllvm4.0 (1:4.0-1ubuntu1~16.04.2 Ubuntu:16.04/xenial-updates, Ubuntu:16.04/xenial-security [amd64])
    Conf libsensors4 (1:3.4.0-2 Ubuntu:16.04/xenial [amd64])
    Conf libgl1-mesa-dri (17.0.7-0ubuntu0.16.04.1 Ubuntu:16.04/xenial-updates [amd64])
     
  19. till

    till Super Moderator Staff Member ISPConfig Developer

    I guess a downgrade should work in this case by downloading and updating to 3.1.6:

    cd /tmp
    wget http://www.ispconfig.org/downloads/ISPConfig-3.1.6.tar.gz
    tar xvfz ISPConfig-3.1.6.tar.gz
    cd ispconfig3_install/install
    php -q update.php

    or you wait until we checked this on monday.
     
  20. Thomas CARTER

    Thomas CARTER New Member

    Did the downgrade but it's still not creating the user. I'll do a fresh LXC container install with 3.1.6.
     

Share This Page