Problem creating MySQL dbs in Multi-Server Configuration

Discussion in 'Installation/Configuration' started by AmpSys, Jan 25, 2011.

  1. AmpSys

    AmpSys New Member

    Hey Guys,

    Long time lurker, first time poster here.

    We are running a multi-server ISPCONFIG3 platform which was setup per the Administrator guide for ISPCONFIG3.

    The configuration is such that there are 5 dedicated ISPCONFIG servers as follows:
    Code:
    [U][B]Server[/B][B]                                            |[/B]                   [B]IP[/B]         [/U]
    web1 (ISPCONFIG console loaded here)              |               10.10.10.15
    mail1                                             |               10.10.10.14
    ns1                                               |               10.10.10.11
    ns2                                               |               10.10.10.13
    db1                                               |               10.10.10.16
    
    The installation guide was followed to the "T" - all servers are Debian Lenny 5.07 VMs.

    There are no issue in creating sites, FTP users, adding/removing/editing DNS records, propagation of these between ns1 and ns2 servers, or problems with mail accounts/databases on mail1.

    However, whenever we create a database & user, it is NOT being created at db1. Ironically, the first website and db we setup did work, but something since has broken and we can't seem to figure it out.

    /etc/mysql/my.cnf has been checked and confirmed to be listening on all interfaces (both web1 and db1).

    Does anyone have any suggestions/advice on what we can check to try and correct?

    Thanks in advance for your help!
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Have you changed the password of the mysql root user on the db server? If yous, please change it in /usr/local/ispconfig/server/lib/mysql_clientdb.conf file too or ispconfig is not able to create databases anymore.
     
  3. AmpSys

    AmpSys New Member

    Hi Till,

    Thanks for the quick reply! There haven't been any password changes to the MySQL root user (at least, best I know), but I'll check the file suggested and advise.

    Question - does it matter if the root account on all servers use the same password?

    *Edit - Confirmed - there have been no password changes after checking the file.
     
    Last edited: Jan 25, 2011
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Please enable logelevel debug for the db server in ispconfig under system > server config. Then run:

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

    on the db server as root and post the output that you get on the screen.
     
  5. AmpSys

    AmpSys New Member

    Here's the output:

    Code:
    db1:~# /usr/local/ispconfig/server/server.sh
    25.01.2011-19:07 - DEBUG - Set Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    25.01.2011-19:07 - DEBUG - No Updated records found, starting only the core.
    25.01.2011-19:07 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
    finished.
    
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    Thats ok.

    1) You are sure that you selected this db server as target for the new database and not one of the other nodes?
    2) Have you deleted any records in the ispconfig database manually or did you try to reset ispconfig somehow after your first working tests?
     
  7. AmpSys

    AmpSys New Member

    Yes, in fact there is no option to select any other servers.

    No - ISPCONFIG db (dbispconfig) has not been touched at all.

    We are getting by through manually creating the dbs on db1 at the shell level which is fine for clients/situations that we manage directly. But until this is sorted out, any designs we had of allowing resellers to create their own webs/dbs through the console is not going to be possible. :(
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Please do these steps to debug your setup:

    1) On the db server, comment out the server.sh cronjob in the root crontab. Run "crontab -e" as root on the db server to edit the crontab and add a # at the beginning of the line and save the file.

    2) Create a new database in ispconfig.

    3) Run:

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

    again on the db server and post the output.

    Additionally I need to know which ISPConfig version you use.
     
  9. AmpSys

    AmpSys New Member

    Output:
    Code:
    db1:~# /usr/local/ispconfig/server/server.sh
    
    Warning: mysql_connect(): Access denied for user 'root'@'localhost' (using password: YES) in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 133
    
    Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /usr/local/ispconfig/server/plugins-available/mysql_clientdb_plugin.inc.php on line 135
    26.01.2011-10:17 - ERROR - Unable to connect to the database
    finished.
    
    Running ISPCONFIG 3.0.3.1

    Strange - running the same command again I get:

    Code:
    db1:~# /usr/local/ispconfig/server/server.sh
    sh: repquota: command not found
    sh: repquota: command not found
    /usr/bin/fail2ban-client
    finished.
    
    And now:
    Code:
    db1:~# /usr/local/ispconfig/server/server.sh
    finished.
    
     
    Last edited: Jan 26, 2011
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    Ok. So the problem is what I suggested in post #2 of this thread. It is not possible to login to the mysql database with the mysql login details that are in the mysql_clientdb.conf file.

    Please ensure that a login with the details in that file as root user to localhost is possible. You can test that with the command:

    mysql -h localhost -u root -p

    and then enter the password that you find in the mysql_clientdb.conf file on the db server.

    Thats not strange, as the transacrtion has been closed and no other transactions are waiting. Also the jobs that are processed at a given time vary as not every monitoriyng process gets called every time you execute the script to ensure that the system does not get overloaded.
     
  11. AmpSys

    AmpSys New Member

    I can't login as localhost but I can login with the FQDN of the server... But I'm using the same password as in the conf file. Why would that be?

    *Edit AGAIN - it's working now!

    I could login to mysql using the FQDN of the server, but not localhost. So I logged in with:

    Code:
    mysql -h {fqdn} -u root -p
    
    Then ran:

    Code:
    mysql > GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY {rootpwd};
    quit
    
    Then created another database , logged in this time as localhost and:

    Code:
    mysql>show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | c1ampmwp           |
    | [B][COLOR="Red"]c1temptemp    [/COLOR][/B]     |
    | c2ampcrm           |
    | dbispconfig        |
    | mysql              |
    +--------------------+
    6 rows in set (0.00 sec)
    
    There it is!

    You're the man, Till! Thanks so much.
     
    Last edited: Jan 26, 2011
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    Mysql has a authentication record for different hostnames and IP addresses, so root@fqdn can have a different password then root@localhost. Seems as if seomone changed the record for the fqdn and not for localhost. Or someone changed the /etc/hosts file so that there is no valid localhost entry in there anymore.

    Check the /etc/hosts file and make sure that localhost is mapped to IP 127.0.0.1. If thats ok, login to your mysql db with phpmyadmin or on the shell and set the correct root password for localhost.
     
  13. till

    till Super Moderator Staff Member ISPConfig Developer

    Mysql has a authentication record for different hostnames and IP addresses, so root@fqdn can have a different password then root@localhost. Seems as if seomone changed the record for the fqdn and not for localhost. Or someone changed the /etc/hosts file so that there is no valid localhost entry in there anymore.

    Check the /etc/hosts file and make sure that localhost is mapped to IP 127.0.0.1. If thats ok, login to your mysql db with phpmyadmin or on the shell and set the correct root password for localhost.
     
  14. dharake

    dharake New Member

    Hi Till, popping in to evesdrop this conversation... my problem is that I cannot login to phpmyadmin with a client's credentials. I have followed the steps that you posted to get debug info, and here is my ouput:


    root@db:~# /usr/local/ispconfig/server/server.sh

    13.05.2012-16:09 - DEBUG - Set Lock: /usr/local/ispconfig/server/temp/.ispconfig _lock
    13.05.2012-16:09 - DEBUG - Found 1 changes, starting update process.
    13.05.2012-16:09 - DEBUG - Replicated from master: REPLACE INTO web_database (`d atabase_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_per m_other`,`server_id`,`type`,`database_name`,`database_user`,`database_password`, `database_charset`,`remote_access`,`remote_ips`,`active`) VALUES ('2','1','2','r iud','ru','','3','mysql','c1test','c1danny2','','','y','','y')
    13.05.2012-16:09 - DEBUG - Calling function 'db_insert' from plugin 'mysql_clien tdb_plugin' raised by event 'database_insert'.
    13.05.2012-16:09 - DEBUG - Created MySQL database: c1test
    13.05.2012-16:09 - DEBUG - Processed datalog_id 9
    13.05.2012-16:09 - DEBUG - Remove Lock: /usr/local/ispconfig/server/temp/.ispcon fig_lock
    finished.

    So in my case it seems to have created the user and database, but I can't access it.

    The steps I took were:
    1. I went into ISPConfig 3.0.4.5 and created a client
    2. Logged in to ISPC as that client
    3. Created new databse and user
    4. Clicked the manage icon which opened up 31.222.179.30:8080/phpmyadmin/
    5. Tried to login with the client db credentials, and get "#1045 Cannot log in to the MySQL server"

    Any help would be very appreciated. Thanks in advance

    ---------------------------

    So turns out after hours of fiddling that the problem was me being a moron....

    in my.cnf I had commented out bind-address like so "#bind" instead of "# bind".... For you newbies, notice the space?

    All works ok now
     
    Last edited: May 13, 2012

Share This Page