problems after new installation on multi server SQL errors

hello ,

i've just installed another server on my pool and something strange start to happen , the version I use is 3.2.11 on debian 12
I've installed the new server following the perfect server procedure ( as always ) after added the server to the pool started the syncronization, by now
all normal , but the synx start to block on SQL errors , one was about web_domain and fastcgi_php_version , that i corrected using a simple query.

Now another problems raise from the ashes of times ...

Code:

Replication of datalog_id: 1148 failed. Error: (software_package) in MySQL server: (localhost) Table 'dbispconfig.software_package' doesn't exist # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)

I go in the SQL pathes and seen that in the last patches that table as been dropped ... from where the sync process on the master ISPConfig took that zombie ? how can i debug or at least restart the process ?
here you can find entire output

Code:

25.09.2023-19:59 - DEBUG [plugins.inc:155] - Calling function 'check_phpini_changes' from plugin 'webserver_plugin' raised by action 'server_plugins_loaded'.
25.09.2023-19:59 - DEBUG [server:177] - Found 4986 changes, starting update process.
25.09.2023-19:59 - WARNING - Falsche Anfrage / Wrong QuerySQL-Query = REPLACE INTO `software_package` (`package_id`,`software_repo_id`,`package_name`,`package_title`,`package_description`,`pack
age_version`,`package_type`,`package_installable`,`package_requires_db`,`package_remote_functions`,`package_key`,`package_config`) VALUES ('1','2','rc2ispconfig3','Webmail - Roundcube','Install
s latest Roundcube with integration to ISPConfig 3',NULL,'app','yes','mysql','server_get,get_function_list,client_templates_get_all,server_get_serverid_by_ip,server_ip_add,server_ip_update,serv
er_ip_delete;client_get_all,client_get,client_add,client_update,client_delete,client_get_sites_by_user,client_get_by_username,client_change_password,client_get_id,client_delete_everything;mail_
user_get,mail_user_add,mail_user_update,mail_user_delete;mail_alias_get,mail_alias_add,mail_alias_update,mail_alias_delete;mail_spamfilter_user_get,mail_spamfilter_user_add,mail_spamfilter_user
_update,mail_spamfilter_user_delete;mail_policy_get,mail_policy_add,mail_policy_update,mail_policy_delete;mail_fetchmail_get,mail_fetchmail_add,mail_fetchmail_update,mail_fetchmail_delete;mail_
spamfilter_whitelist_get,mail_spamfilter_whitelist_add,mail_spamfilter_whitelist_update,mail_spamfilter_whitelist_delete;mail_spamfilter_blacklist_get,mail_spamfilter_blacklist_add,mail_spamfil
ter_blacklist_update,mail_spamfilter_blacklist_delete;mail_user_filter_get,mail_user_filter_add,mail_user_filter_update,mail_user_filter_delete','',NULL) -> 1146 (Table 'dbispconfig.software_pa
ckage' doesn't exist)
25.09.2023-19:59 - ERROR - Replication of datalog_id: 1148 failed. Error: (software_package) in MySQL server: (localhost) Table 'dbispconfig.software_package' doesn't exist # SQL: REPLACE INTO
?? (??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
25.09.2023-19:59 - DEBUG [modules.inc:177] - Replicated from master: REPLACE INTO `software_package` (`package_id`,`software_repo_id`,`package_name`,`package_title`,`package_description`,`packa
ge_version`,`package_type`,`package_installable`,`package_requires_db`,`package_remote_functions`,`package_key`,`package_config`) VALUES ('1','2','rc2ispconfig3','Webmail - Roundcube','Installs
 latest Roundcube with integration to ISPConfig 3',NULL,'app','yes','mysql','server_get,get_function_list,client_templates_get_all,server_get_serverid_by_ip,server_ip_add,server_ip_update,serve
r_ip_delete;client_get_all,client_get,client_add,client_update,client_delete,client_get_sites_by_user,client_get_by_username,client_change_password,client_get_id,client_delete_everything;mail_u
ser_get,mail_user_add,mail_user_update,mail_user_delete;mail_alias_get,mail_alias_add,mail_alias_update,mail_alias_delete;mail_spamfilter_user_get,mail_spamfilter_user_add,mail_spamfilter_user_
update,mail_spamfilter_user_delete;mail_policy_get,mail_policy_add,mail_policy_update,mail_policy_delete;mail_fetchmail_get,mail_fetchmail_add,mail_fetchmail_update,mail_fetchmail_delete;mail_s
pamfilter_whitelist_get,mail_spamfilter_whitelist_add,mail_spamfilter_whitelist_update,mail_spamfilter_whitelist_delete;mail_spamfilter_blacklist_get,mail_spamfilter_blacklist_add,mail_spamfilt
er_blacklist_update,mail_spamfilter_blacklist_delete;mail_user_filter_get,mail_user_filter_add,mail_user_filter_update,mail_user_filter_delete','',NULL)
25.09.2023-19:59 - ERROR - Error in Replication, changes were not processed.
25.09.2023-19:59 - DEBUG [server:217] - Remove Lock: /usr/local/ispconfig/server/temp/.ispconfig_lock
finished server.php.

 

all servers are on 3.2.11
old servers was installed with 3.1 and upgraded every time
the last has been directly on 3.2.5 installed

 

by now i've overridden adding

Code:

CREATE TABLE `software_package` (
`package_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`software_repo_id` int(11) unsigned NOT NULL DEFAULT '0',
`package_name` varchar(64) NOT NULL DEFAULT '',
`package_title` varchar(64) NOT NULL DEFAULT '',
`package_description` text,
`package_version` varchar(8) DEFAULT NULL,
`package_type` enum('ispconfig','app','web') NOT NULL DEFAULT 'app',
`package_installable` enum('yes','no','key') NOT NULL DEFAULT 'yes',
`package_requires_db` enum('no','mysql') NOT NULL DEFAULT 'no',
`package_remote_functions` text,
`package_key` varchar(255) NOT NULL DEFAULT '',
`package_config` text,
PRIMARY KEY (`package_id`),
UNIQUE KEY `package_name` (`package_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `software_repo` (
`software_repo_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`sys_userid` int(11) unsigned NOT NULL DEFAULT '0',
`sys_groupid` int(11) unsigned NOT NULL DEFAULT '0',
`sys_perm_user` varchar(5) DEFAULT NULL,
`sys_perm_group` varchar(5) DEFAULT NULL,
`sys_perm_other` varchar(5) DEFAULT NULL,
`repo_name` varchar(64) DEFAULT NULL,
`repo_url` varchar(255) DEFAULT NULL,
`repo_username` varchar(64) DEFAULT NULL,
`repo_password` varchar(64) DEFAULT NULL,
`active` enum('n','y') NOT NULL DEFAULT 'y',
PRIMARY KEY (`software_repo_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `software_update` (
`software_update_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`software_repo_id` int(11) unsigned NOT NULL DEFAULT '0',
`package_name` varchar(64) NOT NULL DEFAULT '',
`update_url` varchar(255) NOT NULL DEFAULT '',
`update_md5` varchar(255) NOT NULL DEFAULT '',
`update_dependencies` varchar(255) NOT NULL DEFAULT '',
`update_title` varchar(64) NOT NULL DEFAULT '',
`v1` tinyint(1) NOT NULL DEFAULT '0',
`v2` tinyint(1) NOT NULL DEFAULT '0',
`v3` tinyint(1) NOT NULL DEFAULT '0',
`v4` tinyint(1) NOT NULL DEFAULT '0',
`type` enum('full','update') NOT NULL DEFAULT 'full',
PRIMARY KEY (`software_update_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `software_update_inst` (
`software_update_inst_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`software_update_id` int(11) unsigned NOT NULL DEFAULT '0',
`package_name` varchar(64) NOT NULL DEFAULT '',
`server_id` int(11) unsigned NOT NULL DEFAULT '0',
`status` enum('none','installing','installed','deleting','deleted','failed') NOT NULL DEFAULT 'none',
PRIMARY KEY (`software_update_inst_id`),
UNIQUE KEY `software_update_id` (`software_update_id`,`package_name`,`server_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

from an old repo , anway i would like to understand how to avoid it

 

I've inspected in various backups of the db and surprisingly i've found a ghost server .
in the past the cluster got a server with the same name. In the past i've deleted it from the panel but it seems that something remains and after joined a server with same name it has treated as a resteroed old server .

 

Hoping this will help youand other people with same problem

I've tested a new node on ISPC multi server install , with a never used host
servers are all upgraded to 3.2.11
first deploy of the new server started as the first situation ,
now i will report the failed queries and what kind of solution i've used to solve it
Error (on default_logo and custom_logo) :

Code:

30.09.2023-21:14 - ERROR - Replication of datalog_id: 94 failed. Error: (sys_ini) in MySQL server: (localhost) Field 'default_logo' doesn't have a default value # SQL: REPLACE INTO ?? (??,??) VALUES (?,?)....

solution :

Code:

ALTER TABLE `sys_ini` CHANGE `default_logo` `default_logo` TEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL;
ALTER TABLE `sys_ini` CHANGE `custom_logo` `custom_logo` TEXT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL;

Error on warnvirusrecip , warnbannedrecip and warnbadhrecip field :

Code:

30.09.2023-21:27 - ERROR - Replication of datalog_id: 761 failed. Error: (spamfilter_policy) in MySQL server: (localhost) Data truncated for column 'warnvirusrecip' at row 1 # SQL: REPLACE INTO
 ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
30.09.2023-21:27 - ERROR - Error in Replication, changes were not processed.

solution :

Code:

ALTER TABLE `spamfilter_policy` 
CHANGE `warnvirusrecip` `warnvirusrecip` VARCHAR(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT 'N', 
CHANGE `warnbannedrecip` `warnbannedrecip` VARCHAR(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT 'N', 
CHANGE `warnbadhrecip` `warnbadhrecip` VARCHAR(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT 'N';

after these DB mods the sync goes flawlessy , but this new node is just for WEB so i've not installed rspam and others pats that i don't need .
OS :
- master 10 Buster
- slave 12 bookworm
with apache install

 

I'm back after a new installation on multinode system , after this the problems come back again ,
mybe a corrupetd master node db ?

 

Code:

+-----------+--------------------------+-----------+
| server_id | server_name              | dbversion |
+-----------+--------------------------+-----------+
|         1 | 001.****3.it           |        98 |
|         5 | 001.****3.it          |        97 |
|         7 | shared-1.****3.it     |        97 |
|         9 | web-1.****3.it     |        97 |
|        11 | editions.****3.it  |        97 |
|        13 | 003.****3.it           |        97 |
|        14 | 002.****3.it          |        97 |
|        15 | 002.****3.it           |        97 |
|        16 | ns1.****3.com          |        97 |
|        17 | server.****3.com |        97 |
|        18 | 004.****3.it           |        98 |
|        19 | web.****3.it       |        98 |
+-----------+--------------------------+-----------+

servers 17,18 and 19 are those where i must applied mentioned changes in tables