I have upgraded my multi-server installation from 3.2.10p2 to 3.2.11p2. All servers are running Ubuntu 22.04.3 LTS (Jammy Jellyfish). One of the servers (it is running only as dns server) started with the following errors after I run a re-sync job of "maildomains, mailboxes and mailfilter" but only on the mailserver and not the dns server: Code: WARNING - Falsche Anfrage / Wrong QuerySQL-Query = REPLACE INTO `spamfilter_policy` (`id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`policy_name`,`virus_lover`,`spam_lover`,`banned_files_lover`,`bad_header_lover`,`bypass_virus_checks`,`bypass_spam_checks`,`bypass_banned_checks`,`bypass_header_checks`,`spam_modifies_subj`,`virus_quarantine_to`,`spam_quarantine_to`,`banned_quarantine_to`,`bad_header_quarantine_to`,`clean_quarantine_to`,`other_quarantine_to`,`spam_tag_level`,`spam_tag2_level`,`spam_kill_level`,`spam_dsn_cutoff_level`,`spam_quarantine_cutoff_level`,`addr_extension_virus`,`addr_extension_spam`,`addr_extension_banned`,`addr_extension_bad_header`,`warnvirusrecip`,`warnbannedrecip`,`warnbadhrecip`,`newvirus_admin`,`virus_admin`,`banned_admin`,`bad_header_admin`,`spam_admin`,`spam_subject_tag`,`spam_subject_tag2`,`message_size_limit`,`banned_rulenames`,`policyd_quota_in`,`policyd_quota_in_period`,`policyd_quota_out`,`policyd_quota_out_period`,`policyd_greylist`,`rspamd_greylisting`,`rspamd_spam_greylisting_level`,`rspamd_spam_tag_level`,`rspamd_spam_tag_method`,`rspamd_spam_kill_level`) VALUES ('8','1','1','riud','riud','r','Normal - without Spam Subject Change','N','N','N','N','N','','N','N','N','','','','','','','-999.99','4.50','5.50','0.00','0.00','','','','','N','N','N','','','','','','','***SPAM***','0','','-1','24','-1','24','N','n','4.00','6.00','add_header','10.00') -> 1265 (Data truncated for column 'bypass_spam_checks' at row 1) ERROR - Replication of datalog_id: 277690 failed. Error: (spamfilter_policy) in MySQL server: (localhost) Data truncated for column 'bypass_spam_checks' at row 1 # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ERROR - Error in Replication, changes were not processed. Does that look like the SQL database on that dns server got corrupted? How to fix it, as i get every minute the 3 error/warning mails Kind regards KoS
No, its a problem with the data format in master when it get replicated to the slave node. The issue is not caused by the new ISPConfig version though, its caused by the resync. Try to run this SQL query on the slave node in dbispconfig database: Code: ALTER TABLE `spamfilter_policy` CHANGE `bypass_spam_checks` `bypass_spam_checks` VARCHAR(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT 'N';
Thanks @till, this resolved the error. So something went wrong with the sql schema update on ispconfig update? I hope there are no other issues :-(
The issue is unrelated to the update. The issue was caused by the resync. The problem is this: On older systems there can be some records in the master database that do not have 100% correct contents for some fields, this affects mostly the spamfilter_policy table. While older mysql and mariadb versions ignored it and allowed it that e.g. a enum y/n field can be empty or null and ispconfig has no issues with that as well, newer database server versions will throw an error. If you do a resync now, then such old data might get replayed on new systems and this triggers the error. What I basically did with the above SQL statement is to tell the slave system to tolerate such inputs by changing the database field from enum to varchar 1. This has no negative impact on how the system works; it basically just allows the system to emulate the behavior as it was with older mysql and Mariadb versions.
Thanks for the detailed information. It is just weird that the resync caused the issued as for the resync i had only selected the mail-server host and not the nameserver host (on which the errors occured). Shouldn't the "wrong data" be fixed at the source (master table) so that even a resync to the slaves doesn't cause any issue? -> Or will that "wrong data" also cause issue in the future on the master server due to an upgrade of mariadb?
The reason for this is that some records are distributed globally to all nodes (records where server_id = 0), the resync a specific mail system, we also have to resync global records that affect that mail system, and as these are global by nature, we have to resync these globally. Most common records with server_id = 0 are the client records, but spamfilter policies are global as well. Yes, basically, we will have to investigate this in detail in the code and form files and then write an SQL patch that finds the records that should be (Y/N) and sets them to 'N' when they are empty. The above fix was just a quick fix, which has no negative side effects.
