Uncaught mysqli_sql_exception: Data truncated for column 'warnvirusrecip' at row 1

Hello,

I'd like to ask you for help with my issue... updates to the webserver are not sent, jobqueue is stuck, dbispconfig has probably some issues...

root@webserver:~# /usr/local/ispconfig/server/server.sh
01.02.2023-15:10 - WARNING - There is already a lockfile set, but no process running with this pid (175558). Continuing.
PHP Fatal error: Uncaught mysqli_sql_exception: Data truncated for column 'warnvirusrecip' at row 1 in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php:302
Stack trace:
#0 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(302): mysqli_query()
#1 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(339): db->_query()
#2 /usr/local/ispconfig/server/lib/classes/modules.inc.php(171): db->query()
#3 /usr/local/ispconfig/server/server.php(180): modules->processDatalog()
#4 {main}
thrown in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 302

I have multiserver set-up, dedicated ispconfig control panel, web, mail, SQL, NS...

I have installed purly new and clean (minimal install) debian 11 for the new/second web server.
The only thing I installed there is zabbix agent and ISPconfig in multiserver way.

Configuration Updates from control panel were working for this server, but then, they stopped for some reason.
more info:
ISPCONFIG version - main server (control panel) as well as the new clean webserver is: 3.2.9
* 7 /usr/bin/php7.4 74 manual mode
* 6 /usr/bin/php-cgi7.4 74 manual mode

Server version: 10.5.18-MariaDB-0+deb11u1 Debian 11

MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| @@SQL_MODE | @@GLOBAL.SQL_MODE |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+

ANy idea how to resolve this please?
I tried to remove tables from dbispconfig and recreate them from scratch, didn't work...

It might be related to PHP version as I tried to add additional versions into the new web server from control panel, but not sure...changing of PHP version(alternative from OS) didn't work either.

thank you so much
kind regards
j.

 

Start with this: https://forum.howtoforge.com/threads/please-read-before-posting.58408/
Please post listings in CODE tags.

 

Hello,

I had the same issue with a different table (Ispconfig v. 3.2.9p1):

Code:

Mon Mar 27 14:09:02 CEST 2023 27.03.2023-14:09 - DEBUG [server:177] - Found 36 changes, starting update process.
Mon Mar 27 14:09:02 CEST 2023 PHP Fatal error: Uncaught mysqli_sql_exception: Data truncated for column 'pm' at row 1 in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php:302
Mon Mar 27 14:09:02 CEST 2023 Stack trace:
Mon Mar 27 14:09:02 CEST 2023 #0 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(302): mysqli_query(Object(mysqli), 'REPLACE INTO `w...')
Mon Mar 27 14:09:02 CEST 2023 #1 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(339): db->_query('REPLACE INTO `w...', true, Array)
Mon Mar 27 14:09:02 CEST 2023 #2 /usr/local/ispconfig/server/lib/classes/modules.inc.php(171): db->query('REPLACE INTO ??...', true, Array)
Mon Mar 27 14:09:02 CEST 2023 #3 /usr/local/ispconfig/server/server.php(180): modules->processDatalog()
Mon Mar 27 14:09:02 CEST 2023 #4 {main}
Mon Mar 27 14:09:02 CEST 2023 thrown in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 302

Even if I have checked the structure of the affected table (table web_domain in my case) accross all servers, I haven't found any difference.
After that I tried to check which tasks are waiting in the queue (to investigate the root cause), but I was not successful to see the exact tasks anywhere. There is an option "Show Jobqueue" in the Admin panel, however that function shows just very few information without any details.

Therefore I have increased the ID of the last successful task on the affected server (table "server", column "updated") to skip the problematic task in the queue (in fact to skip the whole queue - it would be helpful to know which task was causing the issue) and all new tasks started to be handled properly after that.

Is there any table to see what exactly is waiting in the queue? That would help me to figure out what was causing the issue next time.

Thank you and regards,
Tom

 

Thank you, Till,

howerver that issue is still here:

Code:

Po bře 27 21:32:01 CEST 2023 27.03.2023-21:32 - WARNING - There is already a lockfile set, but no process running with this pid (18204). Continuing.
Po bře 27 21:32:01 CEST 2023 PHP Fatal error:  Uncaught mysqli_sql_exception: Data truncated for column 'pm' at row 1 in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php:302
Po bře 27 21:32:01 CEST 2023 Stack trace:
Po bře 27 21:32:01 CEST 2023 #0 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(302): mysqli_query(Object(mysqli), 'REPLACE INTO `w...')
Po bře 27 21:32:01 CEST 2023 #1 /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php(339): db->_query('REPLACE INTO `w...', true, Array)
Po bře 27 21:32:01 CEST 2023 #2 /usr/local/ispconfig/server/lib/classes/modules.inc.php(171): db->query('REPLACE INTO ??...', true, Array)
Po bře 27 21:32:01 CEST 2023 #3 /usr/local/ispconfig/server/server.php(180): modules->processDatalog()
Po bře 27 21:32:01 CEST 2023 #4 {main}
Po bře 27 21:32:01 CEST 2023 thrown in /usr/local/ispconfig/server/lib/classes/db_mysql.inc.php on line 302

And I have no idea why there is the issue to write the changes to web_domain table.

I have compared the table structure with the second slave server (where everything is working fine) and the structure is exactly the same.
There is no issue with the conections between the master and slave server as the tasks related to different services (dns changes for example) are working fine.

Could you please advise what to check to have more data for the investigation? Even if this looks like the issue with the table web_admin on the slave server, it's structure, permissions and access details are exactly the same as the second slave server has.

Also both rows in the table sys_datalog on the master server (one row from the working slave server and one row from this non-working slave server) look the same.
Also the permissions related to web_domain table in mysql database don't show any issues.

What is the exact mysql query in db_mysql.inc.php(302)? But as the table has the same structure as the second slave server has, the error is probably somewhere else.

Could you please advise?

Thank you.

 

I understand what you are saying. But I have this issue just with the version 3.2.9p1 running on the slave server. Master is running with version 3.2.9p1 too and the tasks for domains hosted on the master server are finished succesfully (so I cannot say there is a bug in version 3.2.9p1).

If I try to add new subdomain on the slave server running on 3.2.7p1 (server_id=4), then everything works fine and I have this record in sys_datalog table (in both databases - the master as well as the slave database):

Code:

a:2:{s:3:"new";a:90:{s:9:"domain_id";s:3:"899";s:10:"sys_userid";s:3:"338";s:11:"sys_groupid";s:3:"338";s:13:"sys_perm_user";s:4:"riud";s:14:"sys_perm_group";s:4:"riud";s:14:"sys_perm_other";s:0:"";s:9:"server_id";s:1:"4";s:10:"ip_address";N;s:12:"ipv6_address";N;s:6:"domain";s:24:"test2.testing2.cz";s:4:"type";s:9:"subdomain";s:16:"parent_domain_id";s:3:"843";s:10:"vhost_type";N;s:13:"document_root";N;s:10:"web_folder";N;s:11:"system_user";N;s:12:"system_group";N;s:8:"hd_quota";s:1:"0";s:13:"traffic_quota";s:2:"-1";s:3:"cgi";s:1:"y";s:3:"ssi";s:1:"y";s:6:"suexec";s:1:"y";s:9:"errordocs";s:1:"1";s:15:"is_subdomainwww";s:1:"1";s:9:"subdomain";s:4:"none";s:3:"php";s:1:"y";s:4:"ruby";s:1:"n";s:6:"python";s:1:"n";s:4:"perl";s:1:"n";s:13:"redirect_type";s:2:"no";s:13:"redirect_path";s:7:"/test2/";s:12:"seo_redirect";N;s:16:"rewrite_to_https";s:1:"n";s:3:"ssl";s:1:"n";s:15:"ssl_letsencrypt";s:1:"n";s:23:"ssl_letsencrypt_exclude";s:1:"n";s:9:"ssl_state";N;s:12:"ssl_locality";N;s:16:"ssl_organisation";N;s:21:"ssl_organisation_unit";N;s:11:"ssl_country";N;s:10:"ssl_domain";N;s:11:"ssl_request";N;s:8:"ssl_cert";N;s:10:"ssl_bundle";N;s:7:"ssl_key";N;s:10:"ssl_action";N;s:14:"stats_password";N;s:10:"stats_type";s:7:"awstats";s:14:"allow_override";s:3:"All";s:17:"apache_directives";N;s:16:"nginx_directives";N;s:18:"php_fpm_use_socket";s:1:"y";s:14:"php_fpm_chroot";s:1:"n";s:2:"pm";s:8:"ondemand";s:15:"pm_max_children";s:2:"10";s:16:"pm_start_servers";s:1:"2";s:20:"pm_min_spare_servers";s:1:"1";s:20:"pm_max_spare_servers";s:1:"5";s:23:"pm_process_idle_timeout";s:2:"10";s:15:"pm_max_requests";s:1:"0";s:16:"php_open_basedir";N;s:14:"custom_php_ini";N;s:15:"backup_interval";s:4:"none";s:13:"backup_copies";s:1:"1";s:17:"backup_format_web";s:7:"default";s:16:"backup_format_db";s:4:"gzip";s:14:"backup_encrypt";s:1:"n";s:15:"backup_password";s:0:"";s:15:"backup_excludes";N;s:6:"active";s:1:"y";s:18:"traffic_quota_lock";s:1:"n";s:16:"proxy_directives";N;s:23:"last_quota_notification";N;s:13:"rewrite_rules";N;s:10:"added_date";N;s:8:"added_by";N;s:21:"directive_snippets_id";s:1:"0";s:16:"enable_pagespeed";s:1:"n";s:9:"http_port";s:2:"80";s:10:"https_port";s:3:"443";s:13:"log_retention";s:2:"10";s:14:"proxy_protocol";s:1:"n";s:25:"folder_directive_snippets";N;s:13:"server_php_id";s:1:"0";s:27:"jailkit_chroot_app_sections";N;s:27:"jailkit_chroot_app_programs";N;s:21:"delete_unused_jailkit";s:1:"n";s:19:"last_jailkit_update";N;s:17:"last_jailkit_hash";N;}s:3:"old";a:90:{s:9:"domain_id";N;s:10:"sys_userid";N;s:11:"sys_groupid";N;s:13:"sys_perm_user";N;s:14:"sys_perm_group";N;s:14:"sys_perm_other";s:0:"";s:9:"server_id";N;s:10:"ip_address";N;s:12:"ipv6_address";N;s:6:"domain";N;s:4:"type";N;s:16:"parent_domain_id";N;s:10:"vhost_type";N;s:13:"document_root";N;s:10:"web_folder";N;s:11:"system_user";N;s:12:"system_group";N;s:8:"hd_quota";N;s:13:"traffic_quota";N;s:3:"cgi";N;s:3:"ssi";N;s:6:"suexec";N;s:9:"errordocs";N;s:15:"is_subdomainwww";N;s:9:"subdomain";N;s:3:"php";N;s:4:"ruby";N;s:6:"python";N;s:4:"perl";N;s:13:"redirect_type";N;s:13:"redirect_path";N;s:12:"seo_redirect";N;s:16:"rewrite_to_https";N;s:3:"ssl";N;s:15:"ssl_letsencrypt";N;s:23:"ssl_letsencrypt_exclude";N;s:9:"ssl_state";N;s:12:"ssl_locality";N;s:16:"ssl_organisation";N;s:21:"ssl_organisation_unit";N;s:11:"ssl_country";N;s:10:"ssl_domain";N;s:11:"ssl_request";N;s:8:"ssl_cert";N;s:10:"ssl_bundle";N;s:7:"ssl_key";N;s:10:"ssl_action";N;s:14:"stats_password";N;s:10:"stats_type";N;s:14:"allow_override";N;s:17:"apache_directives";N;s:16:"nginx_directives";N;s:18:"php_fpm_use_socket";N;s:14:"php_fpm_chroot";N;s:2:"pm";N;s:15:"pm_max_children";N;s:16:"pm_start_servers";N;s:20:"pm_min_spare_servers";N;s:20:"pm_max_spare_servers";N;s:23:"pm_process_idle_timeout";N;s:15:"pm_max_requests";N;s:16:"php_open_basedir";N;s:14:"custom_php_ini";N;s:15:"backup_interval";N;s:13:"backup_copies";N;s:17:"backup_format_web";N;s:16:"backup_format_db";N;s:14:"backup_encrypt";N;s:15:"backup_password";s:0:"";s:15:"backup_excludes";N;s:6:"active";N;s:18:"traffic_quota_lock";N;s:16:"proxy_directives";N;s:23:"last_quota_notification";N;s:13:"rewrite_rules";N;s:10:"added_date";N;s:8:"added_by";N;s:21:"directive_snippets_id";N;s:16:"enable_pagespeed";N;s:9:"http_port";N;s:10:"https_port";N;s:13:"log_retention";N;s:14:"proxy_protocol";N;s:25:"folder_directive_snippets";N;s:13:"server_php_id";N;s:27:"jailkit_chroot_app_sections";N;s:27:"jailkit_chroot_app_programs";N;s:21:"delete_unused_jailkit";N;s:19:"last_jailkit_update";N;s:17:"last_jailkit_hash";N;}}

If I try the same on the slave server running ISPConfig 3.2.9p1 (server_id=3) then ISPConfig is struggling to write that recort into the slave database and I have this record in the master db only:

Code:

a:2:{s:3:"new";a:90:{s:9:"domain_id";s:3:"898";s:10:"sys_userid";s:3:"235";s:11:"sys_groupid";s:3:"235";s:13:"sys_perm_user";s:4:"riud";s:14:"sys_perm_group";s:4:"riud";s:14:"sys_perm_other";s:0:"";s:9:"server_id";s:1:"3";s:10:"ip_address";N;s:12:"ipv6_address";N;s:6:"domain";s:18:"test.testing.cz";s:4:"type";s:9:"subdomain";s:16:"parent_domain_id";s:3:"280";s:10:"vhost_type";N;s:13:"document_root";N;s:10:"web_folder";N;s:11:"system_user";N;s:12:"system_group";N;s:8:"hd_quota";s:1:"0";s:13:"traffic_quota";s:2:"-1";s:3:"cgi";s:1:"y";s:3:"ssi";s:1:"y";s:6:"suexec";s:1:"y";s:9:"errordocs";s:1:"1";s:15:"is_subdomainwww";s:1:"1";s:9:"subdomain";s:4:"none";s:3:"php";s:1:"y";s:4:"ruby";s:1:"n";s:6:"python";s:1:"n";s:4:"perl";s:1:"n";s:13:"redirect_type";s:2:"no";s:13:"redirect_path";s:6:"/test/";s:12:"seo_redirect";N;s:16:"rewrite_to_https";s:1:"n";s:3:"ssl";s:1:"n";s:15:"ssl_letsencrypt";s:1:"n";s:23:"ssl_letsencrypt_exclude";s:1:"y";s:9:"ssl_state";N;s:12:"ssl_locality";N;s:16:"ssl_organisation";N;s:21:"ssl_organisation_unit";N;s:11:"ssl_country";N;s:10:"ssl_domain";N;s:11:"ssl_request";N;s:8:"ssl_cert";N;s:10:"ssl_bundle";N;s:7:"ssl_key";N;s:10:"ssl_action";N;s:14:"stats_password";N;s:10:"stats_type";s:7:"awstats";s:14:"allow_override";s:3:"All";s:17:"apache_directives";N;s:16:"nginx_directives";N;s:18:"php_fpm_use_socket";s:1:"y";s:14:"php_fpm_chroot";s:1:"n";s:2:"pm";s:8:"ondemand";s:15:"pm_max_children";s:2:"10";s:16:"pm_start_servers";s:1:"2";s:20:"pm_min_spare_servers";s:1:"1";s:20:"pm_max_spare_servers";s:1:"5";s:23:"pm_process_idle_timeout";s:2:"10";s:15:"pm_max_requests";s:1:"0";s:16:"php_open_basedir";N;s:14:"custom_php_ini";N;s:15:"backup_interval";s:4:"none";s:13:"backup_copies";s:1:"1";s:17:"backup_format_web";s:7:"default";s:16:"backup_format_db";s:4:"gzip";s:14:"backup_encrypt";s:1:"n";s:15:"backup_password";s:0:"";s:15:"backup_excludes";N;s:6:"active";s:1:"y";s:18:"traffic_quota_lock";s:1:"n";s:16:"proxy_directives";N;s:23:"last_quota_notification";N;s:13:"rewrite_rules";N;s:10:"added_date";N;s:8:"added_by";N;s:21:"directive_snippets_id";s:1:"0";s:16:"enable_pagespeed";s:1:"n";s:9:"http_port";s:2:"80";s:10:"https_port";s:3:"443";s:13:"log_retention";s:2:"10";s:14:"proxy_protocol";s:1:"n";s:25:"folder_directive_snippets";N;s:13:"server_php_id";s:1:"0";s:27:"jailkit_chroot_app_sections";N;s:27:"jailkit_chroot_app_programs";N;s:21:"delete_unused_jailkit";s:1:"n";s:19:"last_jailkit_update";N;s:17:"last_jailkit_hash";N;}s:3:"old";a:90:{s:9:"domain_id";N;s:10:"sys_userid";N;s:11:"sys_groupid";N;s:13:"sys_perm_user";N;s:14:"sys_perm_group";N;s:14:"sys_perm_other";s:0:"";s:9:"server_id";N;s:10:"ip_address";N;s:12:"ipv6_address";N;s:6:"domain";N;s:4:"type";N;s:16:"parent_domain_id";N;s:10:"vhost_type";N;s:13:"document_root";N;s:10:"web_folder";N;s:11:"system_user";N;s:12:"system_group";N;s:8:"hd_quota";N;s:13:"traffic_quota";N;s:3:"cgi";N;s:3:"ssi";N;s:6:"suexec";N;s:9:"errordocs";N;s:15:"is_subdomainwww";N;s:9:"subdomain";N;s:3:"php";N;s:4:"ruby";N;s:6:"python";N;s:4:"perl";N;s:13:"redirect_type";N;s:13:"redirect_path";N;s:12:"seo_redirect";N;s:16:"rewrite_to_https";N;s:3:"ssl";N;s:15:"ssl_letsencrypt";N;s:23:"ssl_letsencrypt_exclude";N;s:9:"ssl_state";N;s:12:"ssl_locality";N;s:16:"ssl_organisation";N;s:21:"ssl_organisation_unit";N;s:11:"ssl_country";N;s:10:"ssl_domain";N;s:11:"ssl_request";N;s:8:"ssl_cert";N;s:10:"ssl_bundle";N;s:7:"ssl_key";N;s:10:"ssl_action";N;s:14:"stats_password";N;s:10:"stats_type";N;s:14:"allow_override";N;s:17:"apache_directives";N;s:16:"nginx_directives";N;s:18:"php_fpm_use_socket";N;s:14:"php_fpm_chroot";N;s:2:"pm";N;s:15:"pm_max_children";N;s:16:"pm_start_servers";N;s:20:"pm_min_spare_servers";N;s:20:"pm_max_spare_servers";N;s:23:"pm_process_idle_timeout";N;s:15:"pm_max_requests";N;s:16:"php_open_basedir";N;s:14:"custom_php_ini";N;s:15:"backup_interval";N;s:13:"backup_copies";N;s:17:"backup_format_web";N;s:16:"backup_format_db";N;s:14:"backup_encrypt";N;s:15:"backup_password";s:0:"";s:15:"backup_excludes";N;s:6:"active";N;s:18:"traffic_quota_lock";N;s:16:"proxy_directives";N;s:23:"last_quota_notification";N;s:13:"rewrite_rules";N;s:10:"added_date";N;s:8:"added_by";N;s:21:"directive_snippets_id";N;s:16:"enable_pagespeed";N;s:9:"http_port";N;s:10:"https_port";N;s:13:"log_retention";N;s:14:"proxy_protocol";N;s:25:"folder_directive_snippets";N;s:13:"server_php_id";N;s:27:"jailkit_chroot_app_sections";N;s:27:"jailkit_chroot_app_programs";N;s:21:"delete_unused_jailkit";N;s:19:"last_jailkit_update";N;s:17:"last_jailkit_hash";N;}}

I appologize for the long output, however I am trying to show that there is no difference in the value for 'pm' column. And the slave db is not updated in this case and the job is still waiting in the queue to be finished (master db is updated so there shouldn't be the wrong value for 'pm' column).

As the structure of the table web_domain is the same in both versions (comparing the mysqldump files as well as the tables in the db) I cannot say this is related to the table structure either.

The rollback to version 3.2.7p1 helps to solve this issue however that's not the final solution.

In the meanwhile I will try to update the second slave server to version 3.2.9p1 to see if the issue will raise as well.

Best regards,
Tom

 

I'm experiencing the same issue. Updated the whole setup to 3.2.9p1 last night.

I then just added a new AlmaLinux 9 node to my multi-server setup (CentOS 7 master) and the base data from the master server couldn't be populated on the new node.

Looks to me like that the replace into query (when $app->db->query($sql, true, $params); happens) is not correctly build with data on the slave:

Example:

Code:

Wed May 24 12:59:02 CEST 2023 24.05.2023-10:59 - ERROR - Replication failed. Error: (spamfilter_policy) in MySQL server: (localhost) Data truncated for column 'warnvirusrecip' at row 1 # SQL: REPLACE INTO ?? (??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??,??) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

EL9 is using MariaDB 10.5, just like Debian 11 so it may be related to some kind of incompatiblity between MariaDB 10.5 and current ISPConfig code which is responsible for replication.

The params array is filled with the correct data, so the problem happens when $app->db->query($sql, true, $params); occurs.

 

Thanks for the heads up.

However, the column structure of warnvirusrecip is using on both Master/new EL9 node enums with Y, N only and all table records have in warnvirusrecip "N" or "Y" as value assigned. Even when dumping the params array I can see that N or Y is assigned for this column of each record.

Even after comparing the tables on both servers I'm not seeing any difference, so I've recreated the tables based on current ispconfig3.sql and I still get the same error.

The only thing that seems to work around this problem is to increase the datalog counter on the master server and/or slave - as necessary.

Really really odd...

 

Finally found the reason.

I found previous records in the datalog table that had no value assigned to this column for some reason, but since these records are processed & used for populating data on a slave, such old datalogs eventually can cause problems.

I will open a tiny enhancement PR that will help debugging such cases.

 

Yes and it was also merged, see: https://git.ispconfig.org/ispconfig/ispconfig3/-/merge_requests/1747