search

Cacti / cacti

Cacti ™
http://www.cacti.net
GNU General Public License v2.0
1.65k stars 406 forks source link

Partial Fix: Display zombie data sources without graphs #2463

Closed camerabob closed 5 years ago

camerabob commented 5 years ago

Describe the bug Data sources cannot be deleted since Cacti believes it is tied to a graph that does not exist. See: https://forums.cacti.net/viewtopic.php?f=21&t=59567&start=15

To Reproduce Unknown how to recreate this

Expected behavior The data source should have gone away with the graph.

Screenshots If applicable, add screenshots to help explain your problem. image Tests done: MariaDB [cacti]> select * from data_template_data where local_data_id = 234; +-----+-----------------------------+---------------+------------------+---------------+--------+----------------------+-------------------------+-----------------------+----------+--------+------------+----------+--------------------------+------------------------+ | id | local_data_template_data_id | local_data_id | data_template_id | data_input_id | t_name | name | name_cache | data_source_path | t_active | active | t_rrd_step | rrd_step | t_data_source_profile_id | data_source_profile_id | +-----+-----------------------------+---------------+------------------+---------------+--------+----------------------+-------------------------+-----------------------+----------+--------+------------+----------+--------------------------+------------------------+ | 352 | 69 | 234 | 48 | 1 | NULL | |host_description| - | XxxxxxxXxxxx - XXX##X - | /24/234.rrd | | on | | 300 | | 1 | +-----+-----------------------------+---------------+------------------+---------------+--------+----------------------+-------------------------+-----------------------+----------+--------+------------+----------+--------------------------+------------------------+ 1 row in set (0.00 sec)

id: 352 local_data_template_data_id: 69 local_data_id: 234 data_template_id: 48 data_input_id: 1 t_name: NULL name: |host_description| - name_cache: XxxxxxxXxxxx - XXX##X - (obfuscated) data_source_path: /24/234.rrd t_active: (blank) active: on t_rrd_step: (blank) rrd_step: 300 t_data_source_profile_id: (blank) data_source_profile_id: 1

MariaDB [cacti]> SELECT DISTINCT -> gti.local_graph_id, -> (CASE WHEN gti.id IS NOT NULL THEN 0 ELSE 1 END) AS Deletable, -> (CASE WHEN gl.id IS NOT NULL THEN 'Found' ELSE 'Missing' END) AS graph_local, -> (CASE WHEN gtg.id IS NOT NULL THEN 'Found' ELSE 'Missing' END) AS graph_template_graph, -> gtg.title, gtg.title_cache -> FROM data_local AS dl -> INNER JOIN data_template_rrd AS dtr -> ON dl.id=dtr.local_data_id -> LEFT JOIN graph_templates_item AS gti -> ON gti.task_item_id=dtr.id -> LEFT JOIN graph_local AS gl -> ON gl.id = gti.local_graph_id -> LEFT JOIN graph_templates_graph as gtg -> ON gtg.local_graph_id = gti.local_graph_id -> WHERE dl.id = 234; +----------------+-----------+-------------+----------------------+-------+-------------+ | local_graph_id | Deletable | graph_local | graph_template_graph | title | title_cache | +----------------+-----------+-------------+----------------------+-------+-------------+ |.............207 |..........0 | Found.......| Missing...................| NULL | NULL........| +----------------+-----------+-------------+----------------------+-------+-------------+ 1 row in set (0.00 sec)

MariaDB [cacti]> SELECT DISTINCT -> gti.local_graph_id, -> (CASE WHEN gti.id IS NOT NULL THEN 0 ELSE 1 END) AS Deletable, -> (CASE WHEN gl.id IS NOT NULL THEN 'Found' ELSE 'Missing' END) AS graph_local, -> (CASE WHEN gtg.id IS NOT NULL THEN 'Found' ELSE 'Missing' END) AS graph_template_graph, -> gtg.title, gtg.title_cache -> FROM data_local AS dl -> LEFT JOIN data_template_rrd AS dtr -> ON dl.id=dtr.local_data_id -> LEFT JOIN graph_templates_item AS gti -> ON gti.task_item_id=dtr.id -> LEFT JOIN graph_local AS gl -> ON gl.id = gti.local_graph_id -> LEFT JOIN graph_templates_graph as gtg -> ON gtg.local_graph_id = gti.local_graph_id -> WHERE dl.id = 234;

+----------------+-----------+-------------+----------------------+-------+-------------+ | local_graph_id | Deletable | graph_local | graph_template_graph | title | title_cache | +----------------+-----------+-------------+----------------------+-------+-------------+ | 207 | 0 | Found | Missing | NULL | NULL | +----------------+-----------+-------------+----------------------+-------+-------------+ 1 row in set (0.00 sec)

image

Desktop (please complete the following information):

Smartphone (please complete the following information):

Additional context Add any other context about the problem here.

cigamit commented 5 years ago

You have can view the orphans in 1.2.2 with a special status from the Data Source page. It's likely this was caused by some issues between versions. But the nice point is, you only have to upgrade to 1.2.2 and the items are easy to spot and cleanup. So, this is duplicate and solved already. But thanks for logging.

stormonts commented 5 years ago

I am on 1.2.2 and am having the same issue. More details here: https://forums.cacti.net/viewtopic.php?f=21&t=59567

netniV commented 5 years ago

Spotting the datasources isn't the issue as @stormonts and @camerabob both identified their sources (I am curious if they show up under the Orphaned list). The issue is that despite them appearing to be orphaned, they also show as undeletable due to a linked entry in graph_local

I had them run the following:

SELECT DISTINCT
   gti.local_graph_id, 
    (CASE WHEN gti.id IS NOT NULL THEN 0 ELSE 1 END) AS Deletable, 
    (CASE WHEN gl.id IS NOT NULL THEN 'Found' ELSE 'Missing' END) AS graph_local, 
    (CASE WHEN gtg.id IS NOT NULL THEN 'Found' ELSE 'Missing' END) AS graph_template_graph, 
    gtg.title, gtg.title_cache
FROM data_local AS dl
LEFT JOIN data_template_rrd AS dtr
ON dl.id=dtr.local_data_id
LEFT JOIN graph_templates_item AS gti
ON gti.task_item_id=dtr.id
LEFT JOIN graph_local AS gl
ON gl.id = gti.local_graph_id
LEFT JOIN graph_templates_graph as gtg
ON gtg.local_graph_id = gti.local_graph_id
WHERE dl.id = ?

@camerabob got:

+----------------+-----------+-------------+----------------------+-------+-------------+
| local_graph_id | Deletable | graph_local | graph_template_graph | title | title_cache |
+----------------+-----------+-------------+----------------------+-------+-------------+
| 207            |         0 | Found       | Missing              | NULL  | NULL |
+----------------+-----------+-------------+----------------------+-------+-------------+
1 row in set (0.00 sec)

and @stormonts got:

+----------------+-----------+-------------+----------------------+-------+-------------+
| local_graph_id | Deletable | graph_local | graph_template_graph | title | title_cache |
+----------------+-----------+-------------+----------------------+-------+-------------+
|          10983 |         0 | Found       | Missing              | NULL  | NULL        |
+----------------+-----------+-------------+----------------------+-------+-------------+
1 row in set (0.00 sec)

So both have the identical state.

cigamit commented 5 years ago

Can you please update data_sources.php and see if you can reproduce this issue. Let us know, or if closed, just go ahead and close.

stormonts commented 5 years ago

I updated "data_sources.php" in my installation and still see the data source listed but I am unable to delete it.

cigamit commented 5 years ago

So, this means that there is still a graph that is referencing the data source. What you need to do is trace down that graph. Are you using Aggregates? Cause there was a recent bug corrected there that kept the assignment of the Data Source to the Aggregate even after the actual Graph was removed. If you have aggregates, do a full sync and save those Aggregates one at a time.

If not, or if that does not resolve the issue, read on. The next thing that you will need to identify the local_data_id (you can see it in the browsers URL when you edit the Data Soruce). Once you have this, run the following query:

SELECT DISTINCT gl.id 
FROM data_local AS dl 
INNER JOIN data_template_rrd AS dtr 
ON dl.id=dtr.local_data_id 
INNER JOIN graph_templates_item AS gti 
ON gti.task_item_id=dtr.id 
INNER JOIN graph_local AS gl 
ON gl.id=gti.local_graph_id 
WHERE dl.id = ?;

Replace the ? with the local_data_id. That will tell you the Graphs using this Data Source.

stormonts commented 5 years ago

For me, that returns the following, but the graph does not exist: mysql> SELECT DISTINCT gl.id FROM data_local AS dl INNER JOIN data_template_rrd AS dtr ON dl.id=dtr.local_data_id INNER JOIN graph_templates_item AS gti ON gti.task_item_id=dtr.id INNER JOIN graph_local AS gl ON gl.id=gti.local_graph_id WHERE dl.id = 14485; +-------+ | id | +-------+ | 10983 | +-------+ 1 row in set (0.00 sec)

graphs

We are using Aggregates, but none of the existing ones are related to this graph. What do you mean by do a full sync?

cigamit commented 5 years ago

It's likely an aggregate graph. Run the following:

SELECT * FROM graph_local WHERE id = ?;

Let's see what it returns.

cigamit commented 5 years ago

Also, run this one:

SELECT * FROM aggregate_graphs_items WHERE local_graph_id = ?'
stormonts commented 5 years ago

mysql> SELECT * FROM graph_local WHERE id = 10983; +-------+-------------------+---------+---------------+---------------------+------------+ | id | graph_template_id | host_id | snmp_query_id | snmp_query_graph_id | snmp_index | +-------+-------------------+---------+---------------+---------------------+------------+ | 10983 | 34 | 155 | 0 | 0 | | +-------+-------------------+---------+---------------+---------------------+------------+ 1 row in set (0.00 sec)

mysql> SELECT * FROM aggregate_graphs_items WHERE local_graph_id = 10983; Empty set (0.00 sec)

cigamit commented 5 years ago

Well, edit that graph and see what it is. You can also select host = 155 and graph template = 34 and find it too. Not sure the exact host or graph template is of course.

stormonts commented 5 years ago

But the graph/aggregate does not exist as shown in the images above. There is no graph/aggregate 10983 to edit.

I did delete the associated host (155) and selected the option to delete all associated sources, but unfortunately that didn't delete this data source either.

camerabob commented 5 years ago

Is there an SQL command to change that data source to be able to be deleted? If we can just 'kill these zombies', I think we'll be fine.

On Thu, Mar 7, 2019 at 9:28 PM stormonts notifications@github.com wrote:

But the graph/aggregate does not exist as shown in the images above. There is no graph/aggregate 10983 to edit. It sounds like my only option is to delete the associated host which will then delete all associated data sources that can't be deleted as they are linked to a non existant graph/aggregate.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Cacti/cacti/issues/2463#issuecomment-470780708, or mute the thread https://github.com/notifications/unsubscribe-auth/AYtTVJF765mVgaOfGWdulK9CJzIcJjAzks5vUcrjgaJpZM4bVBEk .

cigamit commented 5 years ago

No, but in the case of @stormonts the user above, it's not a zombie. What I was thinking of was a way to click on the Data Source and show the Graphs. It's not conducive to a Filter, but it could work.

stormonts commented 5 years ago

Clicking on the Data Source yields nothing useful for me: agg

cigamit commented 5 years ago

@camerabob, I've made a change today to make finding the graph that is holding onto the Data Source easier. Please update to the latest develop and look for the new hyperlinks.

@stormonts, that is a thold problem that appears to have been remedied today.

camerabob commented 5 years ago

Would that be the 1.2.x path?

Excuse any misspellings. I'm still getting used to this iGadget!

On Mar 12, 2019, at 11:24 PM, Jimmy Conner notifications@github.com wrote:

@camerabob, I've made a change today to make finding the graph that is holding onto the Data Source easier. Please update to the latest develop and look for the new hyperlinks.

@stormonts, that is a thold problem that appears to have been remedied today.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

netniV commented 5 years ago

Yes. There are no further developments on v1.1

cigamit commented 5 years ago

@camerabob, it's in develop. Just do a fresh pull and you will see the revised interface. I may make some additional changes this weekend to allow you to see the specific graphs when you edit any affected Aggregate Graph associations. Right now, you still can't figure it out easily. The graphs.php interface is working perfectly though.

netniV commented 5 years ago

Not sure if @camerabob is on 1.2.2 yet?

camerabob commented 5 years ago

I am not yet. No time for testing.

Excuse any misspellings. I'm still getting used to this iGadget!

On Mar 15, 2019, at 8:55 AM, Mark Brugnoli-Vinten notifications@github.com wrote:

Not sure if @camerabob is on 1.2.2 yet?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

camerabob commented 5 years ago

Upgraded to 1.2.2. After running troubleshooting on the zombie data source, I get the below:

image

The top line is the zombie. The second was my control. Started five minutes after. Hovering over 'Issues', I get "Data Source returned Bad Results for snmp_oid". I really don't care because I don't have a use for the data source.

camerabob commented 5 years ago

By the way, I have never had any aggregate graphs in any of my Cacti installs, so that is a moot point.

camerabob commented 5 years ago

QUOTE" @camerabob, I've made a change today to make finding the graph that is holding onto the Data Source easier. Please update to the latest develop and look for the new hyperlinks."

There IS NO GRAPH...

netniV commented 5 years ago

1.2.2 wouldn't contain the fix anyway. Only the latest development branch does until 1.2.3 is released at the end of month. Additionally, whilst your point on not having an aggregate may be correct, there is a new column on the data sources list "Graphs" that will have numbers you can click to take you to the graph/aggregate that is linked to that data source (from what I've read in the code above).

There are two things I can do to help additionally, one is to have access to the cacti instance or the other is to test the changes against a dump of your data (you can email that to developers@cacti.net) if you are willing.

Finally, whilst you may not be SEEING a graph, there is a link to one (as we established above when we did the SQL queries). This change is supposed to help with the SEEING part, not removing the 'zombie' itself.

camerabob commented 5 years ago

The version I just updated to via the repo doesn't contain the graph column in the data sources list. What do I need to pull to get there?

stormonts commented 5 years ago

Not sure if I'm using it wrong or what, but I went to the developer version, and I see the graph column. data_sources

However, when I click on the link, it just takes me to the graph page and doesn't specifically highlight the associated graph. And as shown ealier (https://github.com/Cacti/cacti/issues/2463#issuecomment-470532375), there is no graph or aggregate in existence.

cigamit commented 5 years ago

When you click on the graph link just to the right of the ID column, it should take you to the Graphs page. What is shown when you go there? Upload a screen shot. Also, is that Data Source name blank, or did you obfuscate it?

netniV commented 5 years ago

The version I just updated to via the repo doesn't contain the graph column in the data sources list. What do I need to pull to get there?

You are likely using MASTER rather than DEVELOP 👍

camerabob commented 5 years ago

The version I just updated to via the repo doesn't contain the graph column in the data sources list. What do I need to pull to get there?

Never mind. Upgraded, have the column, and it shows that there is supposed to be a graph connected.

stormonts commented 5 years ago

The data source name is blank; I did not obfuscate it. data_sources

I click on the graph link and it takes me to this page as if it is looking for graph 10981 (based on the URL). after_click

There is no graph with an ID of 10981: no_10981_graph

There is no aggregate with an ID of 10981. no_10981_aggregate

camerabob commented 5 years ago

Obfuscated it. Hold on...

image

The link takes you to the graphs page, but not to any graph or section in particular. I'll show you the section pertaining to this device: image

camerabob commented 5 years ago

14 data sources, 13 graphs...

netniV commented 5 years ago

What I am about to ask isn't meaning to be condescending but, did you click on the numbers on that first row of the data sources? The left side is ordinary graphs, the right side is aggregate.

image

As you have pointed out, none of yours have aggregates, but the data source is showing 1 graph is present using it. When you click on that left number, you will get a custom graph filter:

image

I didn't see that in your screenshot selections, so it's not clear.

stormonts commented 5 years ago

I clicked on the number you highlighted and was taken to the screen below that does not show "Custom Graphs List Applied".

after_click

camerabob commented 5 years ago

Ditto... I go to the generic graphs page as well.

netniV commented 5 years ago

Hmm, can you copy the url from the number (not once you've clicked on it) and provide the portion of the URL after the site base, eg. mine looks like:

/graphs.php?reset=1&custom=true&local_graph_ids=37
camerabob commented 5 years ago

/cacti/graphs.php?reset=1&custom=true&local_graph_ids=207

netniV commented 5 years ago

The strange thing is with my latest dev version, even if I use a number that does not exist, I get a "No Graphs Found" message. I don't get a full list of graphs.

camerabob commented 5 years ago

After updating 'graphs.php', I get the below:

image

FYI, ./lib/graphs.php is required for all other good queries to work with this update.

netniV commented 5 years ago

That's what i'd expect but not what we were hoping for 💣

netniV commented 5 years ago

So I made some minor tweaks to @camerabob's installation only. I got the graph to appear by changing the link between graph_local and graph_template_graph from an INNER (must exist) to a LEFT (graph_template_graph may or may not exist).

After that it showed the following: image

Now, I only tweaked the listing code so any attempts to edit, remove, etc still failed since those were likely trying to find the graph_template_graph record too. So, it would seem like the template being used was removed but it still had data sources linked to them.

cigamit commented 5 years ago

@camerabob, thanks for giving us access to do the deep diagnosis. There will be a change shortly. Please test it out including the ability to remove the Graph.

camerabob commented 5 years ago

An FYI. I just 'upgraded' (via yum upgrade cacti). The version I see online is 1.2.3 but yum still says I have 1.1.36 installed. Whatever.... The zombie process is still present, but I was at least able to stop it from polling and creating errors.

image

image

netniV commented 5 years ago

You have a package installation issue which funnily enough I just messaged you about on the forums to remind you of. The package didn't fully install since 1.1.36 due to a symlink vs real directory issue.

camerabob commented 5 years ago

Excuse any misspellings. I'm still getting used to this iGadget!

On Mar 22, 2019, at 11:00 AM, Mark Brugnoli-Vinten notifications@github.com wrote:

So I made some minor tweaks to @camerabob's installation only. I got the graph to appear by changing the link between graph_local and graph_template_graph from an INNER (must exist) to a LEFT (graph_template_graph may or may not exist).

After that it showed the following:

Now, I only tweaked the listing code so any attempts to edit, remove, etc still failed since those were likely trying to find the graph_template_graph record too. So, it would seem like the template being used was removed but it still had data sources linked to them.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.