DS data dosen't exist anymore but SPINE is still trying to process it

[Rno]

I had an issue with an SNMP temperature polling, I setup an automation rule to graph the temperature of some device.
But when doing so the device (Cisco 4500X) reply with 1500 objects, so I decide not to do so and remove the rule and every Graph of it (including the DS releated).

But now I'm still getting this error every 2 minutes:

Code: Select all

21/05/2024 14:40:01 - POLLER: Poller[Main Poller] PID[527651] WARNING: Poller Output Table not Empty. Issues: 4143, DS[Missing Datasource 93544, Missing Datasource 94111, Missing Datasource 117985, Missing Datasource 101827, Missing Datasource 101830, Missing Datasource 105448, Missing Datasource 155788, Missing Datasource 117904, Missing Datasource 168412, Missing Datasource 78052, Missing Datasource 181144, Missing Datasource 78490, Missing Datasource 206872, Missing Datasource 142993, Missing Datasource 168469], Additional Issues Remain. Only showing first 20 
 

When this happen on the poller output table I have 4143 DS that dosen't exist anymore, at least I can't see them on cacti.
I try to use RRD File checker, RRD File cleaner, but that has no effect.

What can I do ?

[Rno]

I can find all those record inside the 'Data Query Cache Items'
But how can I remove them ?

No one ?

[macan]

Try utilities -> system utilities -> Rebuild Poller Cache

[Rno]

I did it, but the 'object' are not visible in the 'Poller Cache Items', only on the Data Query Cache Items

2024-05-24 14_55_00-Clipboard.png (8.09 KiB) Viewed 1952 times

and the 'Data Query Cache items'

2024-05-24 14_55_00-Clipboard.png (8.09 KiB) Viewed 1952 times

And that's the one I need to clean up

[Rno]

All this wrong record are present inside the poller_item table.
So it' was badly deleted when I delete the graph.

I try to figure out how I can remove the record (how are they linked to another value on another table) and how that happen (looking inside the graphs.php where it's the beginning of the delete action are).

[Rno]

Well it's even worse of what I was thinking!
The only clean table are the one who keep the reference to the rra file, and the one who keep the graph id
All table in between (between 6 and 10) are corrupted with data that doesn't make sense.
And every Spine cycle, it give me tons of error about the Poller Output Table not empty and Missing Datasource

Any developer can help me ?
I don't even know exactly which table are used, and how to clean all of it (linked together or not).

is there any documentation on the table structure up to date ?

About the reason, of this mess, I had to delete 150'000 wrong graphs, and doing 1'000 at time cause the trouble, I think it was the fact that spine was running in the same time than my delete and maybe some table where locked by spine, and the delete doesn't handle that situation. (db_execute doesn't return any value)

[macan]

any error in log? Poller overrun? Boost is enabled?

php /path/to/cacti/cli/audit_database.php
php /path/to/cacti/cli/rebuild_poller_cache.php

[Rno]

It's Cacti 1.2.27 (latest build).

yes some error in the log:

Code: Select all

28/05/2024 09:35:02 - POLLER: Poller[Main Poller] PID[3164422] WARNING: Poller Output Table not Empty. Issues: 4143, DS[Missing Datasource 117886, Missing Datasource 155761, Missing Datasource 168541, Missing Datasource 143284, Missing Datasource 130441, Missing Datasource 92284, Missing Datasource 117613, Missing Datasource 143293, Missing Datasource 143296, Missing Datasource 78466, Missing Datasource 194479, Missing Datasource 156049, Missing Datasource 156052, Missing Datasource 117694], Additional Issues Remain. Only showing first 20

The poller is fine, even when I had 150'000 RRD it was running under 1 minutes, and now here it is:

Code: Select all

28/05/2024 09:58:29 - SYSTEM FLOWVIEW STATS: Time:0.05 Listeners:13 Newrecs:47670 Schedules:0
28/05/2024 09:58:28 - SYSTEM STATS: WEATHERMAP Time:11.56 Maps:8 Warnings:0 Notes:None
28/05/2024 09:58:28 - SYSTEM THOLD STATS: Time:10.96 Tholds:4733 TotalDevices:1258 DownDevices:12 NewDownDevices:0
28/05/2024 09:58:19 - SYSTEM DSSTATS STATS: Time:1.49 Type:HOURLY
28/05/2024 09:58:17 - SYSTEM MONITOR STATS: Time:0.05 Reboots:0 DownDevices:0 Notifications:0 Purges:0
28/05/2024 09:58:17 - SYSTEM SYSLOG STATS: Time:0.04 Deletes:0 Incoming:36 Removes:7 XFers:7 Alerts:4 Alarms:0 Reports:0
28/05/2024 09:58:17 - SYSTEM MAINT STATS: Time:0.01
28/05/2024 09:58:17 - SYSTEM STATS: Time:15.1085 Method:spine Processes:5 Threads:20 Hosts:1258 HostsPerProcess:252 DataSources:22003 RRDsProcessed:0

So for the audit_database (here is what I get with something other than Clean):

Code: Select all

Checking Table: 'automation_devices'                         
ERROR Col: 'snmp_priv_protocol', Attribute 'Type' invalid. Should be: 'char(6)', Is: 'char(7)'

ERRORS: 1, WARNINGS: 0
---------------------------------------------------------------------------------------------
Checking Table: 'automation_snmp_items'                      
ERROR Col: 'snmp_priv_protocol', Attribute 'Type' invalid. Should be: 'char(6)', Is: 'char(7)'

ERRORS: 1, WARNINGS: 0
---------------------------------------------------------------------------------------------
Checking Table: 'host'                                       
ERROR Col: 'snmp_priv_protocol', Attribute 'Type' invalid. Should be: 'char(6)', Is: 'char(7)'

ERRORS: 1, WARNINGS: 0
---------------------------------------------------------------------------------------------
Checking Table: 'poller_item'                                
ERROR Col: 'snmp_priv_protocol', Attribute 'Type' invalid. Should be: 'char(6)', Is: 'char(7)'

ERRORS: 1, WARNINGS: 0
---------------------------------------------------------------------------------------------
Checking Table: 'settings_user'                              
ERROR Col: 'value', Attribute 'Type' invalid. Should be: 'varchar(4096)', Is: 'varchar(2048)'

ERRORS: 1, WARNINGS: 0
---------------------------------------------------------------------------------------------
Checking Table: 'snmpagent_managers'                         
ERROR Col: 'snmp_priv_protocol', Attribute 'Type' invalid. Should be: 'char(6)', Is: 'char(7)'

ERRORS: 1, WARNINGS: 0
---------------------------------------------------------------------------------------------
Checking Table: 'syslog_programs'                             - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_auth'                             - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_config_cache'                     - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_data'                             - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_groups'                           - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_maps'                             - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
Checking Table: 'weathermap_settings'                         - Does not Exist.  Possible Plugin
---------------------------------------------------------------------------------------------
ERRORS are fixable using the --repair option.  WARNINGS will not be repaired
due to ambiguous use of the column.
---------------------------------------------------------------------------------------------

I did the rebuild poller cache, 2 times, I will do it again, I also did 'Rebuild Resource Cache'.

Code: Select all

[root@cacti]# php cli/rebuild_poller_cache.php 
WARNING: Do not interrupt this script.  Rebuilding Poller Cache can take quite some time
There are 1258 hosts, 5 threads and 252 hosts to process per thread

[macan]

fix your db
php /path/to/cacti/cli/audit_database.php --repair

It isn't reason for your problem but it will be better to fix it.

Nntil someone more experienced can advise, we can try found where is something from Cisco - EnvMon - Temperature used.
Go to console -> data collection -> data queries -> find ID of your "Cisco - EnvMon - Temperature".
Next step - SQL:
SELECT sqg.id, gt.name AS graph_template_name, sqg.name, gl.id AS graphs FROM snmp_query_graph AS sqg LEFT
JOIN graph_templates AS gt ON sqg.graph_template_id = gt.id LEFT JOIN graph_local AS gl ON gl.snmp_query_graph_id = sqg.id AND gl.graph_template_id = sqg.graph_template_id WHERE sqg.snmp_query_id = XXX_your_id_XXX ORDER BY sqg.name;

You get list of graph ids which are using something from this cisco data query.

[Rno]

Ok the DB is repaired

The problem with the query you gave me, is that it give me 19 graphs, and those are working correctly.

[macan]

select * from data_template_data where local_data_id=93544;
select * from data_template_data where local_data_id=94111;

[Rno]

What is this local_data_id ?

[macan]

it is your Missing Datasource 93544, Missing Datasource 94111, ...

[Rno]

Ha ha ok sorry

So the return of both query return nothing

[TheWitness]

Run database repair and see if that helps.