CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054'

[ad1mt]

I am trying to move our cacti installation onto a new server with a new version of linux, and new versions of cacti, mysql, rrdtool, snmpd, etc, etc, while preserving our existing graphs & data. this is the latest problem in my attempt achieve this:

10/08/2008 11:00:52 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT graph_tree_items.id, graph_tree_items.title, graph_tree_items.order_key, graph_tree_items.host_id, graph_tree_items.host_grouping_type, host.description as hostname FROM graph_tree_items LEFT JOIN host ON (host.id=graph_tree_items.host_id) LEFT JOIN graph_templates ON (graph_templates_graph.graph_template_id=graph_templates.id) LEFT JOIN user_auth_perms ON ((graph_templates_graph.local_graph_id=user_auth_perms.item_id and user_auth_perms.type=1 AND user_auth_perms.user_id=1) OR (host.id=user_auth_perms.item_id AND user_auth_perms.type=3 AND user_auth_perms.user_id=1) OR (graph_templates.id=user_auth_perms.item_id AND user_auth_perms.type=4 AND user_auth_perms.user_id=1)) WHERE graph_tree_items.graph_tree_id=7 AND ((user_auth_perms.type != 1 OR user_auth_perms.type is null) AND ((user_auth_perms.type != 3) OR (user_auth_perms.type is null)) AND ((user_auth_perms.type != 4) OR (user_auth_perms.type is null))) AND graph_tree_items.local_graph_id = 0 ORDER BY graph_tree_items.order_key"

any ideas anyone?

thanks, mark.

[gandalf]

Pleas run this command from mysql command line and post findings
Reinhard

[ad1mt]

Pleas run this command from mysql command line and post findings
Reinhard

reinhard,

thanks for your help... but what command do I run?

regards,
mark.

[gandalf]

Code: Select all

SELECT graph_tree_items.id, graph_tree_items.title, graph_tree_items.order_key, graph_tree_items.host_id, graph_tree_items.host_grouping_type, host.description as hostname FROM graph_tree_items LEFT JOIN host ON (host.id=graph_tree_items.host_id) LEFT JOIN graph_templates ON (graph_templates_graph.graph_template_id=graph_templates.id) LEFT JOIN user_auth_perms ON ((graph_templates_graph.local_graph_id=user_auth_perms.item_id and user_auth_perms.type=1 AND user_auth_perms.user_id=1) OR (host.id=user_auth_perms.item_id AND user_auth_perms.type=3 AND user_auth_perms.user_id=1) OR (graph_templates.id=user_auth_perms.item_id AND user_auth_perms.type=4 AND user_auth_perms.user_id=1)) WHERE graph_tree_items.graph_tree_id=7 AND ((user_auth_perms.type != 1 OR user_auth_perms.type is null) AND ((user_auth_perms.type != 3) OR (user_auth_perms.type is null)) AND ((user_auth_perms.type != 4) OR (user_auth_perms.type is null))) AND graph_tree_items.local_graph_id = 0 ORDER BY graph_tree_items.order_key

Reinhard

[ad1mt]

the error returned is:

ERROR 1054 (42S22): Unknown column 'graph_templates_graph.graph_template_id' in 'on clause'

mark.

[gandalf]

Please try

Code: Select all

SELECT graph_tree_items.id, graph_tree_items.title, graph_tree_items.order_key, graph_tree_items.host_id, graph_tree_items.host_grouping_type, host.description as hostname FROM graph_tree_items LEFT JOIN host ON (host.id=graph_tree_items.host_id) LEFT JOIN graph_templates_graph ON (graph_templates_graph.graph_template_id=graph_templates.id) LEFT JOIN user_auth_perms ON ((graph_templates_graph.local_graph_id=user_auth_perms.item_id and user_auth_perms.type=1 AND user_auth_perms.user_id=1) OR (host.id=user_auth_perms.item_id AND user_auth_perms.type=3 AND user_auth_perms.user_id=1) OR (graph_templates.id=user_auth_perms.item_id AND user_auth_perms.type=4 AND user_auth_perms.user_id=1)) WHERE graph_tree_items.graph_tree_id=7 AND ((user_auth_perms.type != 1 OR user_auth_perms.type is null) AND ((user_auth_perms.type != 3) OR (user_auth_perms.type is null)) AND ((user_auth_perms.type != 4) OR (user_auth_perms.type is null))) AND graph_tree_items.local_graph_id = 0 ORDER BY graph_tree_items.order_key

and post findings. This would definitively be a bug. So please post cacti version as well (patched? PIA?)
Reinhard

[ad1mt]

the error returned from mysql is:

"ERROR 1054 (42S22): Unknown column 'graph_templates.id' in 'on clause'"

my cacti version is 0.8.7b and when I tried to apply the 3 patches from the web site, for each patch I got:

"Reversed (or previously applied) patch detected! Skipping patch."

so I assume that my version is fully patched.

not sure what you mean by "PIA".

thanks, mark.

[gandalf]

PIA = plugin architecture
This column is definitively part of the standard cacti db. So sth very ugly must have happend. Please run

Code: Select all

 mysql --user=cactiuser -p cacti
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 185
Server version: 5.0.51a Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show columns from graph_templates;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment | 
| hash  | char(32)              | NO   |     |         |                | 
| name  | char(255)             | NO   | MUL |         |                | 
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> quit
Bye

And post the result table
Reinhard

[ad1mt]

here is the output table:

Code: Select all

mysql> show columns from graph_templates;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| hash  | char(32)              | NO   |     |         |                |
| name  | char(255)             | NO   | MUL |         |                |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mark.

[gandalf]

OMG, I'm blind. The SQL is indeed broken. It does not name the table before using in in ON clause.
Please create a bug as given by http://www.cacti.net/bugs.php and add all data from the verify first post.
Reinhard

[ad1mt]

can you tell me which file this code is in, then I can do a quick fix & make some progress.

thanks, mark.

[gandalf]

There's no need to do this. I will do if I have the bug report. And I will givve you the patch, of course.
Reinhard

[ad1mt]

OMG, I'm blind. The SQL is indeed broken. It does not name the table before using in in ON clause.
Please create a bug as given by http://www.cacti.net/bugs.php and add all data from the verify first post.
Reinhard

I did this. My bug report has just been closed by The Witness -

"Make sure strict mode is disabled on the MySQL server. Search the forums using google for the answer."

I have checked my.cnf and strict mode does not appear to be switched on.

I did search the forums, and could not find anything that matched.

mark.

[gandalf]

Please post a link to the bug report. I'll do it
Reinhard

[ad1mt]

Please post a link to the bug report. I'll do it
Reinhard

here is the link:

http://bugs.cacti.net/view.php?id=1325

thanks, mark