Multiserver db sync issues

Discussion in 'Installation/Configuration' started by Pasha Dee, Jan 5, 2019.

  1. Pasha Dee

    Pasha Dee New Member

    Hi guys,
    Been battling this issue for about 2 weeks now. Some guidance would be much appreciated. Bought the ISPConfig manual hoping there would be some more details on debugging in there but unless I missed it it wasn't in there.

    I have the following nodes running:
    web (master db)

    After upgrading them all to 3.1 branch my job que no longer processes.
    I have read all the threads I can find in debugging this with no success.
    What I would like if someone that knows ISP config internal could lay out for me some details on what happens in the background for synchronization to occur and I am competent enough on the command line and with mysql to be able to figure it out I hope.
    For instance when I add a client on the master node (web), I am assuming it now writes that to the database on web, then via cronjob on each node say mail node will connect to master db, check the sys_datalog table and see if there is any information that it needs to add to its local db as well?

    I have tried running manually on each node, none produce any errors. I have tried increasing updated column in server table to completely clear job queue only to end up in the same situation again once new changes are applied.
    mail node did not have (for some reason) the ip and mysql information for the master db in it's /usr/local/ispconfig/server/lib/xxxx_config.php, I added that in but not sure how to force it to reconfigure itself? (I ran php -q update.php after those changes but no luck there either so far). I made sure that on the master db all servers are allowed to connect (and tested via command line mysql -h web -u root -p) from each host... that works...

    If anyone has any clue of what I missed I would be greatly thankful!

    Thank you.
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, that's correct. slave node connects to master by using the dbmaster login details from /usr/local/ispconfig/server/lib/ The query is basically: get all records from sys_datalog where server_id = my server id and datalog_id > the value in updated column of the corresponding server record in server table.
  3. Pasha Dee

    Pasha Dee New Member

    Thanks very much for your response!
    So to do a little finer troubleshooting (as I'm wondering if my mail node somehow has it's ID messed up):
    What do I check to ensure that the sql query constructed is correct (where does each node store it's ID, and whether it is master or not), I'm assuming that'll be a boolean or something in the database that tells is to check, and then it'll look in for the variables to use for the mysql connection there.
    Reason I'm having to do some manual troubleshooting here is because I don't get any errors anywhere.. I can't enable debug in the gui of my master node (I click on the server name but nothing happens). I managed to enable debug on the mail node (I installed gui on that too), but the strange thing there is it only lists itself as an available server to configure, and when I enabled debug there, it enabled it on the master... something's really messed up :)

    Thanks for your time!
  4. Pasha Dee

    Pasha Dee New Member

    So I confirmed one of my suspicions (mentioned in my last post) by adding a line to server.php
    echo (print_r($conf));
    [server_id] => 1 <-- both on my web and mail nodes
    How do I change that on the mail node to match it's ID on the master?
  5. Pasha Dee

    Pasha Dee New Member

    I think I figured out the answer to my last post :)
    I updated the server_id in mysql (on mail node) and changed the same in
    Everything seems to be syncing up properly now.

    Thank you for your help.
    till likes this.
  6. Pasha Dee

    Pasha Dee New Member

    Just thought I'd add a little more to this in case someone is in a similar situation as me.
    My fight was not over after changing server id in config files... since this is the mail server I had to go through every file in /etc/postfix and the sql file in /etc/dovecot update the proper server_id in each query there to match the new server id.... after that I had to go to mysql dbispconfig and change the server_id to the new id for all the talbes in the mail_xxxxxx that contained a server_id column... so a bit of a process :)

    still having an issue with the new user I created dovecot won't allow them to authenticate (or receive any emails) because I believe there's some issue with the Maildir (mail store) settings.. not sure if anyone can point me in the right direction there.
  7. Pasha Dee

    Pasha Dee New Member

    And glad to answer my own question again in case someone can use this down the road :)

    check the mail_user table for the maildir and maildir_format column to be set, for whatever reason the new users maildir_format column was blank so it wasn't building the proper configuration for dovecot (I'm assuming maildir:/var/vmail/domain/user but instead was sending it only /var/vmail/domain/user)

  8. Jesse Norell

    Jesse Norell Well-Known Member Staff Member Howtoforge Staff

    I suspect this part would be fixed if you update the server id in then re-run the ispconfig update.php script on the mail server (specifying the correct master server), and letting it reconfigure services. It shouldn't hurt to do that anyways, and might fix other config files, but I don't think it will update the server_id columns in your db tables.
  9. Pasha Dee

    Pasha Dee New Member

    I kinda of thought about that after I did everything manually, definitely helped me get to know ispconfig internal a lot more than I've ever had to in the past :)

Share This Page