search

mhofer117 / ispconfig-ddns-module

ISPConfig module to support Dynamic DNS updates for users
MIT License
29 stars 5 forks source link

Adding slave DNS #13

Closed kennhardy closed 11 months ago

kennhardy commented 1 year ago

Today I added a third slave dns with a fresh ispconfig install. Upon doing this I noticed no records was synced to bind. Checking the log of cron I had:

Sun Sep 3 13:41:02 CEST 2023 03.09.2023-11:41 - ERROR - Replication of datalog_id: 1524 failed. Error: (ddns_token) in MySQL server: (localhost) Table 'dbispconfig.ddns_token' doesn't exist # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?)

This table is not present in any of my other nameservers. Why was it needed in this third?

Importing the table into this NS3 solved the problem off course. But i'm mostly curious to why the NS1 and NS2 does not have problems with this? Neither of those have the table dbispconfig.ddns_token present.

Or is the dbispconfig.ddns_token table supposed to be present in all instances of ispconfig and I set it up wrong to begin with?

mhofer117 commented 1 year ago

I guess the error log is not related to records not getting synced. It is only a token after all, not a dns record.

The ddns_tokens table is not supposed to be present on all servers, only the main server.

That error log may be present on the all slave dns servers, see issue #11, which was solved with v1. 4, but older tokens / datalog history may still produce the error - it can be ignored.

kennhardy commented 1 year ago

Actually, the above Error made the replication fail completely. So then something strange might be happening if a node is added post installation as I did.

mhofer117 commented 1 year ago

Well that is concerning, you may need to delete related datalog entries or fix their server_id... I could prepare some sql script later if required

kennhardy commented 1 year ago

Not necessary. As i mentioned earlier - adding the table to the new (NS3) node fixed the replication and error has ceased. Just wanted to check if this was a known issue and if not make you aware of it.

mhofer117 commented 1 year ago

Thanks for clarifying.

I investigated on my server and came up with the following SQL script to prevent this issue in the future:

select @server_id := server_id from server
where active=1 and dns_server=1
order by server_id
limit 1;

update ddns_token set server_id = @server_id
where server_id = 0;

update sys_datalog set server_id = @server_id
where server_id = 0 and dbtable = 'ddns_token';

This way, you are not required to create the ddns_token on slave servers (but that does not hurt either).

Would you have a way to test and verify this on your side? I would like to include this as a db migration script.

kennhardy commented 1 year ago

Unfortunately I do not have the possibility to test this on my side as this is a production system. But if you or anyone else want to try it, I more or less just:

After this, the new nodes server.sh script will error out and not perform any replication:

$ tail -f /var/log/ispconfig/cron.log

Sun Sep 3 13:16:01 CEST 2023 03.09.2023-11:16 - ERROR - Replication of datalog_id: 1524 failed. Error: (ddns_token) in MySQL server: (localhost) Table 'dbispconfig.ddns_token' do>
Sun Sep 3 13:16:01 CEST 2023 03.09.2023-11:16 - ERROR - Error in Replication, changes were not processed.
ms217 commented 11 months ago

Unfortunately I do not have the possibility to test this on my side as this is a production system. But if you or anyone else want to try it, I more or less just:

  • Install a fresh Ubuntu 22.04 and add all dependencies

  • Add users to main ispconfig for the new machine

  • Set the ispconfig instance in maintanence mode

  • Run the http://www.ispconfig.org/downloads/ISPConfig-3-stable.tar.gz in advanced mode and use "Join multinode instance".

  • Remove maintenance mode and apply sync from tolls in ispconfig.

After this, the new nodes server.sh script will error out and not perform any replication:

$ tail -f /var/log/ispconfig/cron.log

Sun Sep 3 13:16:01 CEST 2023 03.09.2023-11:16 - ERROR - Replication of datalog_id: 1524 failed. Error: (ddns_token) in MySQL server: (localhost) Table 'dbispconfig.ddns_token' do>
Sun Sep 3 13:16:01 CEST 2023 03.09.2023-11:16 - ERROR - Error in Replication, changes were not processed.

The SQL queries in https://github.com/mhofer117/ispconfig-ddns-module/issues/13#issuecomment-1704644505 should do the trick, because datalog records with server_id = 0 are always replicated to all slave servers.

This also means (regarding the latter error message) that the ddns tables must be present on all slave servers, because the server_id for these records is 0.

The only exception would be if the server_id of the sys_datalog records referring to the DDNS module are changed to the actual DNS server on which the DDNS plugin is also installed and used.

If you are unsure turn on maintenance mode and then make a backup of the master server's database and execute these queries, but adding a new machine to the multi-server setup won't fix the problem until you fix these sys_datalog records.

mhofer117 commented 11 months ago

The SQL fix has been successful in my test-setup. Since no other feedback has been received, it is now included in the 1.4.0 migration script and this issue was closed.