SSL column for multiple pollers can be incorrectly set causing SQL errors

[bmfmancini]

Hey Guys

I am seeing something strange only on debian 10

I am seeing the following errors when creating a remote poller on debian 10

08/21/2019 14:57:43 - INSTALL: always: Updating remote configuration file
08/21/2019 14:57:43 - CMDPHP ERROR: A DB Exec Failed!, Error: Data too long for column 'dbssl' at row 1
08/21/2019 14:57:43 - CMDPHP SQL Backtrace: (/install/background.php[52]:Installer::beginInstall(), /lib/installer.php[3200]:Installer->install(), /lib/installer.php[2715]:Installer->installPoller(), /lib/installer.php[2817]:remote_update_config_file(), /install/functions.php[611]:sql_save(), /lib/database.php[1394]:_db_replace(), /lib/database.php[1324]:db_execute(), /lib/database.php[191]:db_execute_prepared())
08/21/2019 14:57:43 - DBCALL ERROR: SQL Save Failed for Table 'poller'. SQL:'INSERT INTO poller (name, hostname, dbdefault, dbhost, dbuser, dbpass, dbport, dbssl) VALUES ('New Poller', 'localhost', 'cacti', '127.0.0.1', 'cacti', 'cacti', 3306, 'false') ON DUPLICATE KEY UPDATE name=VALUES(name), hostname=VALUES(hostname), dbdefault=VALUES(dbdefault), dbhost=VALUES(dbhost), dbuser=VALUES(dbuser), dbpass=VALUES(dbpass), dbport=VALUES(dbport), dbssl=VALUES(dbssl)'
08/21/2019 14:57:43 - INSTALL: always: Finished INSTALL Process for v1.2.5
08/21/2019 14:57:43 - INSTALL: always: Unable to obtain poller id for this server
08/21/2019 14:57:43 - INSTALL: always: Installation was started at 2019-08-21 19:57:30, completed at 2019-08-21 19:57:43
^C
root@localhost:/var/www/html/cacti/log# uname -a
Linux localhost 4.19.0-5-amd64 #1 SMP Debian 4.19.37-5 (2019-06-19) x86_64 GNU/Linux
root@localhost:/var/www/html/cacti/log# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 10 (buster)
Release: 10
Codename: buster
root@localhost:/var/www/html/cacti/log#

Centos7,Debian9,RHEL7 all good

[netniV]

Now that is strange given that you have the textual 'false' or the existing column value. Can you run

show create table `poller`\G

[bmfmancini]

Ill rebuild the lab give me a moment ill run it

[bmfmancini]

MariaDB [(none)]> use cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cacti]> show create table poller\G
*************************** 1. row ***************************
Table: poller
Create Table: CREATE TABLE poller (
id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
disabled char(2) COLLATE utf8mb4_unicode_ci DEFAULT '',
name varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
notes varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT '',
status int(10) unsigned NOT NULL DEFAULT 0,
timezone varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT '',
hostname varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
dbdefault varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
dbhost varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
dbuser varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
dbpass varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
dbport int(10) unsigned DEFAULT 3306,
dbssl char(3) COLLATE utf8mb4_unicode_ci DEFAULT '',
dbsslkey varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
dbsslcert varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
dbsslca varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
total_time double DEFAULT 0,
max_time double DEFAULT NULL,
min_time double DEFAULT NULL,
avg_time double DEFAULT NULL,
total_polls int(10) unsigned DEFAULT 0,
processes int(10) unsigned DEFAULT 1,
threads int(10) unsigned DEFAULT 1,
sync_interval int(10) unsigned DEFAULT 7200,
snmp mediumint(8) unsigned DEFAULT 0,
script mediumint(8) unsigned DEFAULT 0,
server mediumint(8) unsigned DEFAULT 0,
last_update timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
last_status timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
last_sync timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
requires_sync char(2) COLLATE utf8mb4_unicode_ci DEFAULT '',
PRIMARY KEY (id),
KEY name (name),
KEY disabled (disabled)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pollers for Cacti'
1 row in set (0.000 sec)

[bmfmancini]

Here is my mariadb config

[mariadb-10.3]
innodb_flush_log_at_timeout = 4
innodb_read_io_threads = 34
innodb_write_io_threads = 17
max_heap_table_size = 70M
tmp_table_size = 70M
join_buffer_size = 130M
innodb_buffer_pool_size = 250M
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_file_format = Barracuda
innodb_large_prefix = 1
root@localhost:/etc/mysql/mariadb.conf.d#

[netniV]

dbssl char(3) COLLATE utf8mb4_unicode_ci DEFAULT '',

There's the problem, 'false' isn't a valid value. It's going to expect 'on'/'' (off). I did a quick check and I think the problem may reside in pollers.php, there's a section that sets the column to true or false, which really should be blank or 'on'

[bmfmancini]

Weird so why just on Deb 10 ??

[netniV]

It's not, it's to do with using SSL and a remote poller 👍

[bmfmancini]

hmm interesting but I didnt have the ssl option enabled they were commented out

[bmfmancini]

• Make sure these values reflect your actual database/host/user/password
  */

$database_type = "mysql";
$database_default = "cacti";
$database_hostname = "127.0.0.1";
$database_username = cacti;
$database_password = "password";
$database_port = "3306";
$database_retries = 5;
$database_ssl = "false";
$database_ssl = "false";
$database_ssl = "false";
$database_ssl = "false";

/*

• When the cacti server is a remote poller, then these entries point to
• the main cacti server. Otherwise, these variables have no use and
• must remain commented out.
  */

$rdatabase_type = 'mysql';
$rdatabase_default = 'cacti';
$rdatabase_hostname = 'remote IP ';
$rdatabase_username = 'cacti';
$rdatabase_password = 'password';
$rdatabase_port = '3306';
$rdatabase_retries = 5;
#$rdatabase_ssl = false;
#$rdatabase_ssl_key = '';
#$rdatabase_ssl_cert = '';
#$rdatabase_ssl_ca = '';

[netniV]

Check out the commit, you'll see why 👍

[bmfmancini]

ok will do thanks !