Servers stopped replicaton after Ispconfig update

Discussion in 'ISPConfig 3 Priority Support' started by radim_h, Nov 27, 2023.

Tags:
Page 1 of 2
  1. radim_h

    radim_h Member HowtoForge Supporter

    Hello,
    i'm using ISPConfig3 in cluster for very long time since begginings of ISPC3, in multiserver setup mode
    master-> Admin/DNS , slaves ->mail, web1, web2, db server, mail server
    Now i updated my whole cluster from 3.2p811, also Debian from 11, mailserver from Deb10
    Now cluster stopped replicating
    I have errors like:

    cat /var/log/ispconfig/cron.log
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined variable $state_out in /usr/local/ispconfig/server/lib/classes/plugins.inc.php on line 173
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    Mon Nov 27 01:57:01 AM CET 2023 27.11.2023-00:57 - ERROR - Replication of datalog_id: 358101 failed. Error: (client) in MySQL server: (localhost) Data truncated for column 'locked' at row 1 # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    Mon Nov 27 01:57:01 AM CET 2023 27.11.2023-00:57 - ERROR - Error in Replication, changes were not processed.
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    Mon Nov 27 01:57:01 AM CET 2023 PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    Mon Nov 27 01:57:01 AM CET 2023 finished server.php.

    root@mail:/var/log/ispconfig# cat /var/log/ispconfig/ispconfig.log

    27.11.2023-00:57 - ERROR - Replication of datalog_id: 358101 failed. Error: (client) in MySQL server: (localhost) Data truncated for column 'locked' at row 1 # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    27.11.2023-00:57 - ERROR - Error in Replication, changes were not processed.

    I have updated ISPConfig also with --force option, nothing helps. Database users in master seems correct, slaves can acces master MySQL.

    I had errors also in past after resync, emails like:
    mail.tvujweb.cz - 27.11.2023-07:58 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = REPLACE INTO `client` (`client_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`company_name`,`company_id`,`gender`,`contact_firstname`,`contact_name`,`customer_no`,`vat_id`,`street`,`zip`,`city`,`state`,`country`,`telephone`,`mobile`,`fax`,`email`,`internet`,`icq`,`notes`,`bank_account_owner`,`bank_account_number`,`bank_code`,`bank_name`,`bank_account_iban`,`bank_account_swift`,`paypal_email`,`default_mailserver`,`mail_servers`,`limit_maildomain`,`limit_mailbox`,`limit_mailalias`,`limit_mailaliasdomain`,`limit_mailforward`,`limit_mailcatchall`,`limit_mailrouting`,`limit_mail_wblist`,`limit_mailfilter`,`limit_fetchmail`,`limit_mailquota`,`limit_spamfilter_wblist`,`limit_spamfilter_user`,`limit_spamfilter_policy`,`limit_mail_backup`,`limit_relayhost`,`default_xmppserver`,`xmpp_servers`,`limit_xmpp_domain`,`limit_xmpp_user`,`limit_xmpp_muc`,`limit_xm..................
    But it never stopped syncing...

    There is over 2000 changes waiting to propagate , as i did Resync of ISPConfig. Do you have idea, what should i check?
    - Is there any way to check if master DB is properly altered after update, or slaves, i'm thinkign avout iconsistency od DBs
    - Or may i lets say copy db from master and make it slave database
    (this way i did long time ago, when i as splitting the servers ?)

    - Maybe use migraton tool ?
    There is about 150 websites, about 900 mailboxes, i'm kind of stucked :((

    It may be coincidence, but 2 servers still have not updated Debian and those seems to replicate without problems. Both are webservers.
    One of those is Deb9 (cannot be updated, because of prehistoric PHP versions), second one is Debian 11 - this one was added regular way, not by splitting DB in far past.. On DEB9 i'm not sure, how it was done.

    One of broken servers i db server, i;m sure it was also added the reguar way, but it also doesnt replicate


    I'm willing to pay for extra support, use ISPConfig migrate tool, whatever it needs to get the webhosting cluster working again

    Regards
    RH
     
    Last edited: Nov 27, 2023
    radim_h, Nov 27, 2023
    #1
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    The problem is that the database structure of one or more slave nodes does not match the one of the master which means that columns are missing in some of the tables now. It might be that the updater was unable to apply some or all SQL patch files e.g. when the mysql root password in /usr/local/ispconfig/server/lib/mysql_clientdb.conf is not correct. compare the client table between master and that slave node to check which column is missing on the slave.

    The migration tool is not needed to fix this.

    You can contact @Th0m here if you need help by remote login: https://www.ispconfig.org/get-support/?type=ispconfig
     
    till, Nov 27, 2023
    #2
  3. radim_h

    radim_h Member HowtoForge Supporter

    Hello Till,
    thank you for info

    Are you sure, that the problem is in "client" table ?
    Then i can delete table on the slave and import it from the master ?
     
    radim_h, Nov 27, 2023
    #3
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Most likely, yes.

    No, I won't do that, this can cause further issues. Compare the table structure in phpmyadmin between the two, most likely there are one or more fields missing. Create the missing fields using phpmyadmin and the waiting jobs shall start to be processed.
     
    till, Nov 27, 2023
    #4
  5. radim_h

    radim_h Member HowtoForge Supporter

    Hello,
    you were right.
    On the slaves there are missing some rows with client records.
    Do you think i can simply take clients table from master and copy i onto slave.. ?
     
    radim_h, Nov 27, 2023
    #5
  6. radim_h

    radim_h Member HowtoForge Supporter

    I will answer to myself. it didnt help..
    question is, what do do now :(
     
    radim_h, Nov 27, 2023
    #6
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    No, please don't do that as this might just give you more issues. First, test if login to the local database on the slave nodes works correctly trough IP 127.0.0.1 and localhost for the root user and the password from file /usr/local/ispconfig/server/lib/mysql_clientdb.conf. You can test that on the slave nodes like this:

    mysql -h localhost -u root -p
    mysql -h 127.0.0.1 -u root -p

    And did you got any errors during ISPConfig update?

    If the MySQL root logins are fine, then we must try to find out which SQL patch file need to be re-applied. Do you remember which exact ISPConfig version you used before the update?
     
    till, Nov 27, 2023
    #7
  8. radim_h

    radim_h Member HowtoForge Supporter

    HEllo,
    connect to localhost are working.
    It seems theat problem is in missing records in tables, which wasnt replicated before.
    Or there should have benn a problem when updating mariaDB which should have skipped some records

    Yes i got errors exactly for localhost vs 127.0.0.1
    fixed those then re-runned update, also did with --force...
    I kinda did too much things in one run. Update Debian, Update ISpconfig, on 5 servers.. Realized too late replication is not running

    Databases after udpate have default collating utf8mb3_general_ci
    this is ok ?
     
    radim_h, Nov 27, 2023
    #8
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    The collation should be fine. Do you know which exact ISPConfig version you had before the update?
     
    till, Nov 27, 2023
    #9
  10. radim_h

    radim_h Member HowtoForge Supporter

    3.2.8p2
    but there were WARINGsr when resyncing also with this version... but it worked
     
    Last edited: Nov 27, 2023
    radim_h, Nov 27, 2023
    #10
  11. radim_h

    radim_h Member HowtoForge Supporter

    There are missing two users in client table...
    i can delete those, byt i'm not sure there is not missing more data somewhere...

    This is error im getting by mail,when crobntab enabled

    db1.XXXXX.cz - 27.11.2023-12:02 - ERROR - Replication of datalog_id: 358101 failed. Error: (client) in MySQL server: (localhost) Data truncated for column 'locked' at row 1 # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
     
    radim_h, Nov 27, 2023
    #11
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    If they are in the master, then these are likely in the pending requests, so they get added automatically when the underlaying issue is resolved.

    Please do not change data, we must fix the underlying issue to get this working again. Changing data will just complicate things.

    please run the following SQL queries in the databases of the affected slave nodes:

    Code:
    ALTER TABLE `sys_user` ADD `otp_type` SET('none', 'email') NOT NULL DEFAULT 'none' AFTER `lost_password_reqtime`, ADD `otp_data` VARCHAR(255) NULL AFTER `otp_type`, ADD `otp_recovery` VARCHAR(64) NULL AFTER `otp_data`, ADD `otp_attempts` TINYINT NOT NULL DEFAULT '0' AFTER `otp_recovery`;
ALTER TABLE `mail_user` CHANGE `quota` `quota` BIGINT(20) NOT NULL DEFAULT '0';
ALTER TABLE `server_php` ADD `sortprio` INT(20) NOT NULL DEFAULT '100' AFTER `active`;
ALTER TABLE `mail_user` ADD COLUMN `imap_prefix` varchar(255) NULL default NULL AFTER `backup_copies`;
-- #6456 comodoca.com needs to become sectigo.com
UPDATE `dns_ssl_ca` SET `ca_issue` = 'sectigo.com' WHERE `ca_issue` = 'comodo.com';
UPDATE `dns_ssl_ca` SET `ca_issue` = 'sectigo.com' WHERE `ca_issue` = 'comodoca.com';
UPDATE `dns_ssl_ca` SET `ca_name` = 'Sectigo (formerly Comodo CA)' WHERE `ca_issue` = 'sectigo.com';
    These are all SQL changes between 3.2.8p1 and the current version. I just wonder why your SQL errors above are about the client table, while the client table has not been altered in these ISPConfig versions.
     
    till, Nov 27, 2023
    #12
  13. radim_h

    radim_h Member HowtoForge Supporter

    #1060 - Duplicate column name 'otp_type'
    #1060 - Duplicate column name 'sortprio'
    #1060 - Duplicate column name 'imap_prefix'
    etc.. so chnges are there ?
     
    radim_h, Nov 27, 2023
    #13
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes. So there must be other differences. Have you compared the client table structure (not its content, only the structure)?
     
    till, Nov 27, 2023
    #14
  15. radim_h

    radim_h Member HowtoForge Supporter

    IF records are missing on client table on slave, those will not be added ?
    as there is missing some rows, which are on master.. which is weird, like 10 client back are mising two lines... rest is there

    maybe some wrong characters on master, or, i'm suspecting longer time cwispys cript for WHMCS for creating webs, there was in past mesages about clients, i usually saved client on master and it has been fixed.. it is cnnecting to SIPConfig via API, so such erors may not be created

    there maybe old inconsistencies...
    Mybe, better approach may be use master table.. maybe i'm fu*ked :\
     
    radim_h, Nov 27, 2023
    #15
  16. radim_h

    radim_h Member HowtoForge Supporter

    Structute seems to be OK by eye... better do it via SQL, but i'm not sure how..
     
    radim_h, Nov 27, 2023
    #16
  17. till

    till Super Moderator Staff Member ISPConfig Developer

    Missing records get added when the resync gets processed.

    This should not be related to wrong characters.

    That#s all fixable, so don't worry. Have you tried to get in touch with Thom?

    Are the errors in debug mode still the same?
     
    till, Nov 27, 2023
    #17
  18. radim_h

    radim_h Member HowtoForge Supporter

    Hello,
    i have downloaded some SQL analyzer.
    In clients, structure is same. In data, there are missing two clients on target.

    I wrote Thom, no answer yet
    [​IMG] [​IMG]
     
    radim_h, Nov 27, 2023
    #18
  19. till

    till Super Moderator Staff Member ISPConfig Developer

    If the structure is the same, then the replication should work now. Please run server.sh on that slave node to see which error you are getting now.
     
    till, Nov 27, 2023
    #19
  20. radim_h

    radim_h Member HowtoForge Supporter

    I found that there was no record in one LOCKED colum in client table.
    Question?
    Can i somehow delete those 3000 chnanges waiting to sync ? as there is waiting changes to write with previous data ?
    I can also try to delete user, where is stops everytime

    Also do not understand:
    Undefined array key "hostname"

    RESULT:
    root@db1:/var/log/ispconfig# /usr/local/ispconfig/server/server.sh
    PHP Warning: Undefined variable $state_out in /usr/local/ispconfig/server/lib/classes/plugins.inc.php on line 173
    PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    27.11.2023-14:10 - ERROR - Replication of datalog_id: 358101 failed. Error: (client) in MySQL server: (localhost) Data truncated for column 'locked' at row 1 # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    27.11.2023-14:10 - ERROR - Error in Replication, changes were not processed.
    PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    PHP Warning: Undefined array key "hostname" in /usr/local/ispconfig/server/lib/app.inc.php on line 324
    finished server.php.
     
    radim_h, Nov 27, 2023
    #20
Page 1 of 2

Share This Page