How to cleanup old / problematic datalog entries?

Hey there,
we run ISPConfig in Multi-Server mode for quite some time now. Recently we come across more and more issues which are starting to annoy us enough that we want to solve them
It's mostly about adding new servers to the Setup and migrating systems. After that ISPConfig tries to sync the whole datalog over there. At least that how it looks like. And maybe the second issue is a follow up of the first ... let's see.

There are two particular annoying issues:
1. There are some "ghost websites" which were deleted on the master and the UI a couple of years ago but are still somewhere in the system with a server id of "0". On each new server with "webserver" enabled they get created with the web folders, nginx vhosts, system users and such. We manually delete all this stuff after each new server setup. The datalog is the only place where any reference to these sites remains and that is synced with new servers. Is there any way to safely delete those zombies from the datalog? Or might there be another place where these zombies might hide?

To be more precise: There are several (~914) entries with a "server_id" of 0 in the datalog table of the master which seem to be the problematic entries.

2. The datalog replication runs into SQL errors du do updated table schemas. In particular with a newly setup server:

ERROR - Replication of datalog_id: 28190 failed. Error: (web_domain) in MySQL server: (localhost) Unknown column 'fastcgi_php_version' in 'field list' # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
ERROR - Replication of datalog_id: 28190 failed. Error: (web_domain) in MySQL server: (localhost) Unknown column 'enable_spdy' in 'field list' # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Our current workaround is to re-add these recently dropped columns back to the DB. But that's not really a great solution...
Yet without solving these errors the new server is never fully in sync with the master and as such not really usable.

In general we expect both issues to reside in the datalog somewhere. And I wasn't able to find anything about "how to cleanup your datalog".

Appreciating any help

Thank you and best regards,

Jan

 

Till: Thank you very much as always

Deleting all faulty entries with server_id = 0 from the sys_datalog seems to instantly fix that stuff. Yet I would not have dared to simply do so without your confirmation ;-). I like easy solutions!