Poller Database Sync Issues

[mmccaugh]

I am still running down the scope here so apologies for the incomplete information.

I have a single master server (Which is also a poller) and then 5 more distributed pollers, today when troubleshooting my upgrade to 1.2.3 I noticed some log files were writing to old directories, upon further checking I was able to ascertain that while I log in to each poller and confirm things like the cacti log path are accurate in the GUI, but if I check in the MySQL database they appear to be pointed to the old directories.. And the logs are writing to this directory.

What is even MORE perplexing is :

[root@cacti01 log]# pwd
/var/www/html/cacti-1.2.3/log
[root@cacti01 log]# ls -al cacti.log
-rw-r--r--. 1 apache apache 212 Apr 8 20:11 cacti.log
[root@cacti01 log]# cd ../../cacti-1.1.36/log
[root@cacti01 log]# ls -al cacti.log
-rw-r--r--. 1 apache apache 660 Apr 8 20:57 cacti.log
[root@cacti01 log]# date
Mon 8 Apr 20:58:20 BST 2019

I was writing to the new location, but it appears to have reverted to the old (2 updates ago) directory..

Simple answer here is, is there an approved way for me to force the pollers to pull the out of sync data from the master server?

I may have other issues, so I am still researching.. For instance the version number in the remote poller databases DID update automatically (Before I had even deployed the new files to those pollers..), so if there are schema changes that could explain a lot as they were likely not applied as upgrade_database.php saw it as a move from 1.2.3 to 1.2.3 not what is really was..

I'll post more detail as I have it, but if anyone else has experienced this and has some direction it would be appreciated!

[mmccaugh]

OK another update here, this issue 'may' be self inflicted, but definitely due to updates that have not applied properly.

what I have done so far is export the db schemas from all of my pollers to compare.

mysqldump -u <cacti username> -p -h <cacti poller> --no-data cacti ><output filename>

I was able to confirm these are wildly different, but was struggling with how to identify what version of the schema each represented.

Looking at 1_2_2.php I see this modification -
db_install_execute("ALTER TABLE poller_time MODIFY COLUMN id bigint(20) unsigned auto_increment");

I checked this field in the schemas I saved and in 2 of the 6 I see this field is a bigint(20), in the other 4 it is a mediumint(8) so I am manually working backwards to identify the schemas, then once I have a good idea where each database is I am going to stop the poller, manually set the version in MySQL, run the upgrade_database script which will hopefully fix my issue.. Once the schemas are all accurate I can troubleshoot further, but until these are in sync it's hard to know where issues may or may not be!

[mmccaugh]

Quick clarification, looking at the upgrade_database.php it looks like I can pass a version with the --forcever flag so no need to update version in MySQL.

[mmccaugh]

OK digging through this I am finding massive gaps, some pollers will have modifications from a later upgrade script, but are missing predating updates..

Reading all of these so far I do not see anything that will break if it is run against a table that's already updated.. So next step here is to do a full MySQL dump on a poller, and runt he upgrade_database script with a forcever of 1.1.36 (Which was my initial version on these deployments). If it blows everything up I have the backup to fall back to but it seems to be the cleanest solution in absense of any more intelligent script to sync the schema to the current version. (Which had I more time I would try to write..)

I'll update on how this goes..

[netniV]

You should be able to run the upgrade_database.php with --forcever=<previous version> to run through the required upgrade steps. Changing the poller_time's id field to bigint was a change made as part of 1.2.2, so should have been applied when doing the 1.2.2 upgrade step.

[mmccaugh]

You should be able to run the upgrade_database.php with --forcever=<previous version> to run through the required upgrade steps. Changing the poller_time's id field to bigint was a change made as part of 1.2.2, so should have been applied when doing the 1.2.2 upgrade step.

That did indeed work, the perplexing thing was in some cases there were gaps where a newer update was applied but older ones had not been, so I just did the update with the forcever=1.1.36 which did work.

I found another issue however (And I suspect definitely causing me issues) somehow some of my servers I set to utf8 properly, others are still on the default latin.. I am researching if changing this is as simple as just updating it or if there is additional work needed.. (I think you actually wrote a check for this, but it may have been added after my initial setup.. So this is self inflicted)

Currently the ONLY 'functional' issue I am having is that automation no longer works, I define a network, but when I try to activate discovery I get an error in my cacti log.

2019/04/15 19:00:43 - AUTOM8 [PID: 9684] Network Discover is now running for Subnet Range '31'
2019/04/15 19:00:43 - ERROR PHP NOTICE: Undefined index: dns_servers in file: /var/www/html/cacti-1.2.3/poller_automation.php on line: 362
2019/04/15 19:00:43 - CMDPHP PHP ERROR NOTICE Backtrace: (/poller_automation.php[345]:discoverDevices(), /poller_automation.php[362]:CactiErrorHandler())
2019/04/15 19:00:43 - ERROR PHP NOTICE: Undefined index: name in file: /var/www/html/cacti-1.2.3/poller_automation.php on line: 763
2019/04/15 19:00:43 - CMDPHP PHP ERROR NOTICE Backtrace: (/poller_automation.php[345]:discoverDevices(), /poller_automation.php[763]:CactiErrorHandler())
2019/04/15 19:00:43 - AUTOM8 [PID: 9686] Network Thread 1 Finished, 0 IPs Scanned, 0 IPs Responded to Ping, 0 Responded to SNMP, 0 Device Added, 0 Graphs Added to Cacti

I assumed the issue was something to do with the broken database sync but I have no clue.. My first priority is to fix the collation, and character sets, get the databases properly in sync again, and then troubleshoot as right now it's impossible to say what could be breaking due to the known issues.

[mmccaugh]

https://www.a2hosting.com/kb/developer- ... base-utf-8

Found a guide that seems to cover off all the parts of updating the schema (Noting i changed to utf8mb4/utf8mb4_unicode_ci not the generic listed in this guide)

[mmccaugh]

Actual Steps Performed -

USE cacti

SELECT @@character_set_database, @@collation_database;

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "cacti";

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "cacti" AND T.table_name = "settings";

mysql --database=cacti -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1; "}' | mysql --database=cacti

ALTER DATABASE cacti CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This appears to have worked and my database schemas are MUCH closer to each other now (And a diff is actually readable)

[mmccaugh]

OK another update. I believe I have the database issues more or less mopped up, but I still get the same error on the automation scans.

Oddly enough if I manually execute the scan :

php -q poller_automation.php --network=31 --poller=3 --debug --force

It runs without error..

I am getting my sites added now then will circle back and try to pin down why this works when executed from the CLI, but not through the browser..

[netniV]

Likely a SELinux context issue or user permission error (website user)

[mmccaugh]

SELinux was first thing I checked, I'm actually really comfortable with it but no dice there.. Also confirmed permissions (Though I agree thats how it seemed to me too)

I actually from the system stepped through the code to see what we were doing, and was able to confirm the process breaks at the mysqldump, which also does fail when I try to execute it manually.. What is infuriatingly confusing about this is I CAN dump the db schema, and similarly had no issues dumping the ENTIRE database just a day earlier (Which I did prior to upgrading).

It's almost as if the new tables are not being properly permissioned, but that makes no sense.

Either way I am 99% sure this issue cannot be cacti related, the same code works on my dev box and the mysqldump command fails from a command line, I looked at what the analyze does and didn't think it would really help me so I stopped digging, but it could be indicative if a larger issue I will have to circle back to.

Though I do suppose this could tie in to the sync issues I see and the automation being broken.. I am looking at some fixes on github that were released for database.php now to see if those are potentially part of my issue.

[mmccaugh]

OK I see what's wrong here, this may be a bug.

Code: Select all

      $dns = trim($network['dns_servers']);

This is like 362 of poller_automation.php

Code: Select all

       $network = db_fetch_row_prepared('SELECT *
                FROM automation_networks
                WHERE id = ?',
                array($network_id));

A few lines above we see how the $network variable is constructed.

2137 Query SELECT * FROM automation_networks WHERE id = '32'

Enabling MySQL logging I determined this query is running on the poller (Against the cacti database on said poller). It worked from the command line on my master because even though I specified pollerid=3 it still ran from the master, NOT the remote poller.

Looking at cacti.automation_networks on the remote poller it does not contain any of these beyond the 'test network' I set up on install.. This is failing because nothing exists in the database on the poller (should it?).

It looks like this behavior was potentially changed and it used to run from the master and is now housed on the poller, but the database information has not populated? But I am still digging so I am unsure where the issue lies so far.

I believe (and am going to test next) that if I manually insert the proper rows into the automation_networks table on the remote poller I will get farther, but I suspect I will simply run into another error as there is likely an underlying issue.

[mmccaugh]

mysqldump -u root -p cacti automation_networks automation_snmp automation_snmp_items

Dumping these 3 tables on the master and importing on the poller did the trick, at least partially. The servers were all detected properly, the log shows SNMP responded but the GUI shows it did not still.

I am going to hold and wait for feedback, if this is working for everyone else then it has to be an issue specific to me (IE if others pollers are properly replicating these tables?), if this is NOT an issue specific to me and I just happen to be the first person to find it due to my heavy use of the automated scans then I will continue documenting what I find and submit an issue on github.

If this is just me then I am going to focus on the MySQL database issues, as thats almost certainly the root of my problems.

[mmccaugh]

I am building a server pair (master and poller) in virtualbox to test and see if I have these same issues.

If I do then that more or less answers the question, and if I don't it still answers the question lol..

[netniV]

Most tables should be synchronised between the master and poller databases, purely so that if the master goes offline, it can still know what devices etc it has locally. This suggests that the pushing out of data isn't working via the remote_agent.php