Main poller goes down if add new remote one

[antras]

Installation details:
RHEL 7.5
Packages installed from yum:
cacti 1.1.38
php 5.4.16
mariadb 5.5.56
httpd 2.4.6

While we have just single poller (on central cacti server, ip 10.10.10.118) everything works fine

Code: Select all

$database_type = 'mysql';
$database_default = 'cactidev';
$database_hostname = '10.10.10.118';
$database_username = 'cactiuser';
$database_password = 'cactiuser';
$database_port = '3306';
$database_ssl = false;
$poller_id = 1;

Then we install new poller, complete installation via web for it and start to see it as New Poller in Data Collectors list (in Console).

Code: Select all

$database_type = 'mysql';
$database_default = 'cactidev';
$database_hostname = '10.10.10.117';
$database_username = 'cactiuser';
$database_password = 'cactiuser';
$database_port = '3306';
$database_ssl = false;
$rdatabase_type = 'mysql';
$rdatabase_default = 'cactidev';
$rdatabase_hostname = '10.10.10.118';
$rdatabase_username = 'cactiuser';
$rdatabase_password = 'cactiuser';
$rdatabase_port = '3306';
$rdatabase_ssl = false;
$rpoller_id = 3;

However on next polling cycle Main Poller goes to Down state and each second we see the following in /var/log/cacti/cacti.log on Main server:

Code: Select all

09/10/2018 13:09:41 - CMDPHP SQL Backtrace: (/poller.php: 560 process_poller_output) (/lib/poller.php: 390 db_fetch_assoc_prepared) (/lib/database.php: 402 cacti_debug_backtrace)
09/10/2018 13:09:41 - CMDPHP SQL Backtrace: (/poller.php 535 db_fetch_cell_prepared) (/lib/database.php: 281 cacti_debug_backtrace)
09/10/2018 13:09:41 - DBCALL ERROR: SQL Assoc Failed!, Error:2006, SQL:'SELECT po.output, po.time, UNIX_TIMESTAMP(po.time) as unix_time, po.local_data_id, dl.data_template_id, pi.rrs_path, pi.rrd_name, pi.rrd_num FROM poller_output AS po INNER JOIN poller_item AS pi ON po.local_data_id=pi.local_data_id AND po.rrd_name=po.rrd_name=pi.rrd_name INNER JOIN data_local AS dl ON dl.id=po.local_data_id ORDER BY po.local_data_id LIMIT 40000'
09/10/2018 13:09:41 - DBCALL ERROR: SQL Assoc Failed!, Error: MySQL server has gone away

We've tried to play with different settings in /etc/cacti/db.php, tried with spine - result is always the same.
If we disable New remote poller in Data Collectors via cacti console, Main poller comes back online.

It is possible to login to mysql cactidev database on both servers - we've tested it from both servers.

Can anyone explain what we do wrong?
What configuration we should use remote polling to start working?

[netniV]

The problem here is with MySQL itself. You are losing connection to the MySQL database which may be down to load or something else crashing it. Check your MySQL logs for more information.

[antras]

The problem here is with MySQL itself. You are losing connection to the MySQL database which may be down to load or something else crashing it. Check your MySQL logs for more information.

No errors I can see on both servers (main and remote pollers) in /var/log/mariadb/mariadb.log.
Last message there is from starting of MariaDB.

[netniV]

09/10/2018 13:09:41 - DBCALL ERROR: SQL Assoc Failed!, Error: MySQL server has gone away

This is the line that shows there was a MySQL issue.

[antras]

09/10/2018 13:09:41 - DBCALL ERROR: SQL Assoc Failed!, Error: MySQL server has gone away

This is the line that shows there was a MySQL issue.

Does it mean Main poller lost access to Main database?
Can you explain why everything works when remote poller is disabled? (there is no issue between main poller and main database in that case).

[netniV]

I would say that it is a problem with either the main or the remote poller. One of them does not have the correct config.php configuration. The remote needs both database configurations.

[antras]

I would say that it is a problem with either the main or the remote poller. One of them does not have the correct config.php configuration. The remote needs both database configurations.

I've checked configuration, performed many fresh installations, tried to use default names for db/user/pass - still the same issue.
Once main poller knows that there is one more remote poller and it's enabled - main one stops working.
My configuration is above (in first post). What is wrong?
Any idea about how to look for the problem? Any ideas about what it may be?

[netniV]

Enable everything, watch it fail. Then use MySQL command line or PhpMyAdmin and run the SQL statement shown above. Since there are no question marks in the parameters it should be a straight forward SQL statement with zero parameters. See what result you get.

[Osiris]

Check you max_connections. Also, what DB version?

[browniebraun]

Then we install new poller, complete installation via web for it and start to see it as New Poller in Data Collectors list (in Console).

Code: Select all

$database_type = 'mysql';
$database_default = 'cactidev';
$database_hostname = '10.10.10.117';
$database_username = 'cactiuser';
$database_password = 'cactiuser';
$database_port = '3306';
$database_ssl = false;
$rdatabase_type = 'mysql';
$rdatabase_default = 'cactidev';
$rdatabase_hostname = '10.10.10.118';
$rdatabase_username = 'cactiuser';
$rdatabase_password = 'cactiuser';
$rdatabase_port = '3306';
$rdatabase_ssl = false;
$rpoller_id = 3;

I'm wondering if "$rpoller_id = 3;" is only a typo here in the forum. But if this is really the case within your config.php of the remote poller then this poller instance will act as main poller (default ID=1) due to the reason that $poller_id has not be defined.
It has to be "$poller_id=3;" without the "r" in front.

Regards
-Andi

[netniV]

That's a good spot, I hadn't noticed that myself. We should probably handle that better by failing the system if rdatabase is set and poller_id is blank or 1.

[antras]

Osiris reminded me that I did installation of cacti with default MariaDB settings. Issue was not with max_connections, but with other settings.
After I've added the following ones into my.cnf problem has gone:
max_allowed_packet=32M
tmp_table_size=128M
join_buffer_size=128M

Many thanks for all your suggestions! We can go further now

p.s.
It was typo with rpoller_id, we have poller_id instead.