Remote Poller: Connection test fails during install

[vKnmnn]

Hi Team,

I have been trying to install a remote poller for a few days now, but the installer fails due to the connection test failing. After finding other bug reports about a similar issue #3459, i upgraded the remote poller to 1.2.12, but nothing changes.

The SQL error log shows this when i click the test connection button: 2020-05-09T09:20:04.892578Z 13655082 [Note] Got an error reading communication packets 2020-05-09T09:20:04.892708Z 13655083 [Note] Got an error reading communication packets

The connection is set up with SSL and I have confirmed, that the certificates are valid and the poller user can access the files. Any hints on how i can debug this?

Remote Poller: Ubuntu 18.04, PHP 7.2.24, MySQL 14.14, cacti 1.2.12 Main Poller: Ubuntu 18.04, PHP 7.2.24, MySQL 14.14, cacti 1.2.11

[netniV]

Did you upgrade both ends? Upgrading the remote will have its files reset back to whatever version the main poller is on once a sync is performed.

[vKnmnn]

That is good to know. But i think it won't have an influence because right now it can't sync. I have upgraded the main poller to 1.2.12 now and i don't see a change.

[vKnmnn]

I have recreated the user in the DB on the main poller without REQUIRE X509 and i get a successful connection now.

There seems to be something wrong with the SSL part of the connection. OpenSSL says the certs are valid. In what way is cacti even involved in the SSL part of the process? is there a way to debug this? are there certain no-no-settings in openssl that i could have set?

Obviously this isn't a satisfactory solution, since i need the database connection to be encrypted.

[TheWitness]

Yea, if you are playing with SSL connections, you might be out on the edge of Valhalla. Try the easy way first, then goto the secure route.

[TheWitness]

If you find any tips, help out the rest of the community by create a pull request in the correct area of the documentation GitHub repo.

[vKnmnn]

Yeah of course I'm trying the easy way now, and I'm willing to make a PR on the docs, I just hoped to get some pointers on how cacti does those things from someone who has insight into the code.

[netniV]

Basically the settings in the config.php are applied when the database is opened by lib/database.php. These are simply passed to the MySQL client libraries.

[vKnmnn]

Thank you. I will see if I can find out what's going on.

[netniV]

I may have a chance tonight to take a peek at what you find otherwise it’ll be later in the week

[TheWitness]

If you allow Cacti to simply accept the server certificates, you can use a simplified SSL configuration, you don't have to provide all the details. However, when we first released, we required all 4 settings, which meant that the clients required the private keys and certificate files for all connections.

In Cacti 1.2.12 you can go with the simplified setup which does not require all 4 settings.

[TheWitness]

Refer to this page on the minimum requirements for using SSL. It's pretty clear.

https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html

[TheWitness]

The problem with REQUIRE X509 is maintaining a valid ticket I imagine, that would not be a good things for service account IMHO unless you setup a service principal, which is a bit beyond my reach.

[TheWitness]

I'm going to assume this is resolved now. Don't forget to do a pull request on Enabling SSL Database Encryption in Cacti documentation topic.

[vKnmnn]

I found that i did everything as i was supposed to, according to Oracles Documentation. Thing is i can actually connect to the database with the client. it's just cacti that can't.

is there a way to see if spine would be able to connect? does it log that?

I've come across some hints, that the problem might be that pdo tries to validate the CN of the certificates. I didn't understand very well what cn is being matched by who, but i imagine, if i did, that one could work around those limits with the hosts file maybe? See this link thoughts?

[TheWitness]

Show how you connect using the MySQL cli. If you have settings in your my.cnf.d directory, provided them.

[vKnmnn]

connection from main to remote:

$ sudo -u www-data mysql  -u maincacti -p --ssl-cert=/usr/share/cacti/client_mainserver.crt --ssl-key=/usr/share/cacti/client_mainserver.key --ssl-ca=/etc/mysql/ca.crt -h remoteserver -P 3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25196
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:          25196
Current database:
Current user:           maincacti@mainserver
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.30-0ubuntu0.18.04.1 (Ubuntu)
Protocol version:       10
Connection:             remoteserver via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 6 days 18 hours 47 min 59 sec

Threads: 1  Questions: 1414708  Slow queries: 0  Opens: 1389  Flush tables: 1  Open tables: 1110  Queries per second avg: 2.413

Connection from remote to main:

$ sudo -u www-data mysql -u cacti_remoteuser -p --ssl-cert=/opt/cacti/client_remote.crt --ssl-key=/opt/cacti/client_remote.key --ssl-ca=/etc/mysql/ca.crt -h mainserver -P 3306 cacti
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 108669
Server version: 5.7.30-0ubuntu0.18.04.1-log (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:          108669
Current database:       cacti
Current user:           cacti_remoteuser@remoteserver(ip)
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.30-0ubuntu0.18.04.1-log (Ubuntu)
Protocol version:       10
Connection:            mainserver via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 10 hours 10 min 45 sec

Threads: 22  Questions: 1204133  Slow queries: 124  Opens: 89317  Flush tables: 1  Open tables: 1967  Queries per second avg: 32.859
--------------

on the main server, this is the mysqld.cnf file. in the both servers' directories there's another file called mysqld_safe_syslog.cnf, which only contains the lines [mysqld_safe] and syslog

$ cat /etc/mysql/mysql.conf.d/mysqld.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysql]
default_character_set           = utf8mb4

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /sql/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0
#require_secure_transport = ON
#
# * Fine Tuning
#
#
## this is needed because cacti expects 0000-00-00 00:00:00 to be valid in a few cases.
## these are the defaults minus NO_ZERO_DATE
## NO_ZERO_DATE will be replaced by some "strict" setting in a future version
#sql-mode= "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sql-mode= "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
## recommended settings by cacti
collation_server        = utf8mb4_unicode_ci
init-connect            ='SET NAMES utf8'
character-set-server    = utf8mb4
#character-set-client   = utf8
max_heap_table_size     = 2048M
tmp_table_size          = 512M
join_buffer_size        = 1024M
innodb_buffer_pool_size = 7820M
innodb_flush_log_at_timeout = 3
innodb_read_io_threads  = 32
innodb_write_io_threads = 16
innodb_buffer_pool_instances = 64
key_buffer_size         = 16M
max_allowed_packet      = 256M
thread_stack            = 192K
thread_cache_size       = 8
#wait_timeout = 5
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 1024
innodb_flush_method   = O_DIRECT
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /logs/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log          = 1
slow_query_log_file     = /logs/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
ssl-ca=/etc/mysql/ca.crt
ssl-cert=/etc/mysql/mainserver.crt
ssl-key=/etc/mysql/mainserver.key

on the remote server, i have this config file, that gets included last, because its name starting with "z" there's also /etc/mysqld.conf.d/mysqld.conf, which is the default ubuntu one.

$ cat /etc/mysql/mysqld.conf.d/z_cacti.cnf
sql-mode= "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
## recommended settings by cacti
collation_server                = utf8mb4_unicode_ci
character-set-server            = utf8mb4
max_heap_table_size             = 2048M
init-connect                    ='SET NAMES utf8'
tmp_table_size                  = 512M
join_buffer_size                = 1024M
innodb_file_format              = Barracuda
innodb_large_prefix             = 1
innodb_buffer_pool_instances    = 64
innodb_flush_log_at_timeout     = 3
innodb_buffer_pool_size         = 7820M
innodb_write_io_threads         = 16
innodb_read_io_threads          = 32
max_allowed_packet              = 256M
thread_cache_size               = 8
key_buffer_size                 = 16M
#wait_timeout                   = 5
thread_stack                    = 192K

; If you have SSD disks, use this suggestion.
; If you have physical hard drives, use 200 * the number of active
; drives in the array. If using NVMe or PCIe Flash,
; much larger numbers as high as 100000 can be used.
innodb_io_capacity              = 600
; same note as above but 2000*number of drives
innodb_io_capacity_max          = 6000

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options          = BACKUP
innodb_flush_method             = O_DIRECT
#table_open_cache               = 64
#thread_concurrency             = 10
bind-address = 0.0.0.0
ssl-ca=/etc/mysql/ca.crt
ssl-cert=/etc/mysql/server_remoteserver.crt
ssl-key=/etc/mysql/server_remoteserver.key