local_graph_id messed up when using THOLD, WEATHERMAP

[mikecarel]

Hi to all;

I'm having problem with my graphs views when using Aggregate. Some of the plug-ins cannot get the correct local_graph_id when it is also being used by Aggregate. One of it is Thold which uses the Aggregate graphs as default instead of its original graph, another is Weathermap editor, when picking datasources the local_graph_id reflected was the aggregated graph.

I've been trying to locate and correct this manually in the cacti database but I can't find it. Hope someone can help me on this.


Tnx,

Mike

[gandalf]

Which versions of Cacti & plugins are you using?
Would you be able to pm a gzipped db dump with a hint to find those "weird" stuff?
R.

[mikecarel]

I've found something:

Queried found: 7850(local_data_id of the problematic graph)

mysql> select graph_templates_item.local_graph_id, title_cache FROM graph_templates_item,graph_templates_graph,data_template_rrd where graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id and task_item_id=data_template_rrd.id and local_data_id=7850 LIMIT 1;
+----------------+---------------------+
| local_graph_id | title_cache |
+----------------+---------------------+
| 8366 | RCY2CR000 - Board 1 |
+----------------+---------------------+
1 row in set (0.00 sec)


mysql> select graph_templates_item.task_item_id, graph_templates_item.local_graph_id, title_cache FROM graph_templates_item,graph_templates_graph,data_template_rrd where graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id and task_item_id=data_template_rrd.id and local_data_id=7850 LIMIT 1;
+--------------+----------------+---------------------+
| task_item_id | local_graph_id | title_cache |
+--------------+----------------+---------------------+
| 15763 | 8366 | RCY2CR000 - Board 1 |
+--------------+----------------+---------------------+
1 row in set (0.00 sec)


Query correction: Adding "order by graph_templates_item.local_graph_id ASC limit 1" , will get the correct local_graph_id ()

mysql> select graph_templates_item.task_item_id, graph_templates_item.local_graph_id, title_cache FROM graph_templates_item,graph_templates_graph,data_template_rrd where graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id and task_item_id=data_template_rrd.id and local_data_id=7850 order by graph_templates_item.local_graph_id ASC limit 1;
+--------------+----------------+-----------------------------------------------------+
| task_item_id | local_graph_id | title_cache |
+--------------+----------------+-----------------------------------------------------+
| 15762 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
+--------------+----------------+-----------------------------------------------------+
1 row in set (0.00 sec)

This should be the correct local_graph_id.

mysql>



Removing query LIMITS:

Ex. mysql> select graph_templates_item.task_item_id, graph_templates_item.local_graph_id, title_cache FROM graph_templates_item,graph_templates_graph,data_template_rrd where graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id and task_item_id=data_template_rrd.id and local_data_id=7850 ;
+--------------+----------------+-----------------------------------------------------+
| task_item_id | local_graph_id | title_cache |
+--------------+----------------+-----------------------------------------------------+
| 15763 | 8366 | RCY2CR000 - Board 1 |
| 15763 | 8366 | RCY2CR000 - Board 1 |
| 15763 | 8366 | RCY2CR000 - Board 1 |
| 15763 | 8366 | RCY2CR000 - Board 1 |
| 15763 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15763 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15763 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15763 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15763 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15763 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15763 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15763 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15762 | 8366 | RCY2CR000 - Board 1 |
| 15762 | 8366 | RCY2CR000 - Board 1 |
| 15762 | 8366 | RCY2CR000 - Board 1 |
| 15762 | 8366 | RCY2CR000 - Board 1 |
| 15762 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15762 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15762 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15762 | 7616 | RCY2CR000-Traffic - GE1/0/8 to RRPRPE000 - GE3/0/1 |
| 15762 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15762 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15762 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
| 15762 | 8418 | RCY2CR000 Aggregate Link to RRPRPE000 |
+--------------+----------------+-----------------------------------------------------+
24 rows in set (0.21 sec)


The problem was the result of mysql query. Need to refine the sql to select the correct local_graph_id.
That is why thold and other plugins, is selecting wrong local_graph_id.
I don't know if it's better to add "order by graph_templates_item.local_graph_id ASC limit 1" to get the correct local_graph_id, as my local_graph_id's for the original graph are always lower than the aggregate graphs.

Changing thold.php $grapharr to:
$grapharr = db_fetch_assoc("SELECT DISTINCT local_graph_id FROM graph_templates_item WHERE $sql order by local_graph_id ASC");

and Weathermap cacti-pick.php $SQL_graphid to:

$SQL_graphid = "select graph_templates_item.local_graph_id, title_cache FROM graph_templates_item,graph_templates_graph,data_template_rrd where graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id and task_item_id=data_template_rrd.id and local_data_id=$dataid order by graph_templates_item.local_graph_id ASC limit 1;";


Mike

[gandalf]

good find. Will look into it
R.

[gandalf]

This is an issue with THOLD and WEATHERMAP. So I move it to THOLD and place a hook for WEATHERMAP
R.

[mikecarel]

Sorry for recalling this issue, as the $grapharr(thold.php) was not corrected in thold 0.4.9-3. Using the original thold.php. you will also have problem when using RPN. "ERROR: RPN Expression is invalid!" since the selected graph_id(thold_data) during creation or assigning the graph to a thold template was using the aggregated graph_id. Setting again the $grapharr to:

$grapharr = db_fetch_assoc("SELECT DISTINCT local_graph_id FROM graph_templates_item WHERE $sql order by local_graph_id ASC");

will correct the issue, but you have to delete and re-assign again the graph for thold to correct the graph_id.


Mike

[TheWitness]

You must be using an old version of Thold in that the current version has been doing this for quite some time.

[mikecarel]

I'm using thold 0.4.9-3, tried to download it twice in thold plugin page but got the same, even for the SVN Rev.2006 but its also not in there.

Thanks,

Mike

[TheWitness]

Can you change the query to this and let me know if it solves the issue. I understand it now. Gandalf will tell you that it often time takes me a few passes to comprehend. Here is the revised query, there are a few lines:

Code: Select all

$rrdsql   = array_rekey(db_fetch_assoc("SELECT id FROM data_template_rrd WHERE local_data_id=$rra ORDER BY id"), "id", "id");
$sql      = "task_item_id IN (" . implode(", ", $rrdsql) . ") AND graph_template_id>0";
$grapharr = db_fetch_assoc("SELECT DISTINCT local_graph_id FROM graph_templates_item WHERE $sql");

Let me know if the list is more appropriate. Aggregates present some challenges.

[mikecarel]

it works, the aggregate associated graph also not appearing after using the new query.

$rrdsql = array_rekey(db_fetch_assoc("SELECT id FROM data_template_rrd WHERE local_data_id=$rra ORDER BY id"), "id", "id");
$sql = "task_item_id IN (" . implode(", ", $rrdsql) . ") AND graph_template_id>0";
$grapharr = db_fetch_assoc("SELECT DISTINCT local_graph_id FROM graph_templates_item WHERE $sql");


Thanks,


Mike