Graph Export to FTP, SQL Assoc Failed!, Error:'1054'

helzerr · Post by **helzerr** » Wed Feb 06, 2008 10:34 am

I recently upgraded from 0.8.6j to 0.8.7a and discovered graph export via "FTP (remote) - use php functions" method is now broken.

The Cacti log file indicates that, despite the "Export Directory" setting of /cacti (which I verified is correct in the settings table), somehow the 'remotedir' variable in lib/graph_export.php is getting set to "export_temporary_directory"/cacti-ftp-temp instead. This causes the upload to fail with "FTP Remote directory /tmp/cacti-ftp-tmp does not exist!. Export can not continue." error.

I checked the 0.8.61 and 0.8.7a lib/graph_export.php files with diff, and reverted lines 152 and 165 to the 0.8.6j version, which has resolved the problem for now.

Code: Select all

0.8.6j #152: $_SESSION["sess_config_array"]["path_html_export"] = $stExportDir;
0.8.7a #152: $config["config_options_array"]["path_html_export"] = $stExportDir;

0.8.6j #165: unset($_SESSION["sess_config_array"]["path_html_export"]);
0.8.7a #165: unset($config["config_options_array"]["path_html_export"]);

Is this issue a bug in the new graph_export.php code, or some strange incompatibility with my system configuration? Is the 0.8.7a graph export via FTP working for anyone else?

[edit: I should also mention this is on Linux, with PHP version 5.2.5-pl1-gentoo]

- Ron -

shrek3166 · Post by **shrek3166** » Thu Feb 07, 2008 12:26 pm

Hi,

We have the same issue on linux (fc6) with the 0.8.7a
thanks to your post, we revert back the line and it solves the issue.

best regards,

Nicolas

helzerr · Post by **helzerr** » Fri Feb 08, 2008 8:43 am

Thanks for verifying the problem, Nicolas.

I have submitted this bug in Mantis:

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

- Ron -

helzerr · Post by **helzerr** » Thu Feb 14, 2008 5:23 pm

Interestingly, version 0.8.7b resolved the "FTP Remote directory does not exist!" error despite using the same lib/graph_export code as 0.8.7a on lines 152 and 165.

Now there is a new problem preventing graphs from being exported from 0.8.7b, I am seeing numerous errors like these during the graph export phase:

Code: Select all

02/14/2008 05:20:14 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT DISTINCT graph_templates_graph.id, graph_templates_graph.local_graph_id, graph_templates_graph.height, graph_templates_graph.width, graph_templates_graph.title_cache, graph_templates.name, graph_local.host_id FROM (graph_tree_items, graph_templates_graph) LEFT JOIN graph_local ON (graph_templates_graph.local_graph_id=graph_local.id) LEFT JOIN graph_templates ON (graph_templates.id=graph_local.graph_template_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_templates_graph.local_graph_id<>0) 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_local.host_id=0) AND (graph_templates_graph.export='on')) ORDER BY graph_templates_graph.title_cache"

02/14/2008 05:20:14 PM - 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=1 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"

Running the first SQL query against the database in phpMyAdmin reuslts in:

#1054 - Unknown column 'host.id' in 'on clause'

And the second SQL query results:

#1054 - Unknown column 'graph_templates_graph.graph_template_id' in 'on clause'

[Edit: mySQL version 5.0.54, Generic Cacti 0.8.7b with no add-ons.]

- Ron -

kasimong · Post by **kasimong** » Fri Feb 15, 2008 5:55 am

Hi use the the classic Export Method
but it dont work.

Cacti Version 0.8.7b
Cacti OS unix
SNMP Version net-snmp
RRDTool Version RRDTool 1.2.x
Hosts 62
Graphs 1413
Data Sources Script/Command: 2
SNMP: 727
SNMP Query: 1046
Script Query - Script Server: 31
Total: 1806
Poller Information

02/15/2008 11:50:24 AM - EXPORT FATAL ERROR: Create directory '/opt/cacti/web-root/share/htdocs/static/cacti/graphs' failed. Can not continue
02/15/2008 11:35:25 AM - EXPORT FATAL ERROR: Create directory '/opt/cacti/web-root/share/htdocs/static/cacti/graphs' failed. Can not continue
02/15/2008 11:20:28 AM - EXPORT FATAL ERROR: Create directory '/opt/cacti/web-root/share/htdocs/static/cacti/graphs' failed. Can not continue

Wolfgang

helzerr · Post by **helzerr** » Sat Feb 16, 2008 4:43 am

Wolfgang,

Perhaps the user your poller is running as doesn't have permission to write files in /opt/cacti/web-root/share/htdocs/static/?

- Ron -

kasimong · Post by **kasimong** » Mon Feb 18, 2008 4:53 am

Hi,
the user cacti create the directory with the first export and then i have the error

wolfgang

Post by **gandalf** » Wed Feb 20, 2008 11:26 am

helzerr wrote:Interestingly, version 0.8.7b resolved the "FTP Remote directory does not exist!" error despite using the same lib/graph_export code as 0.8.7a on lines 152 and 165.

Now there is a new problem preventing graphs from being exported from 0.8.7b, I am seeing numerous errors like these during the graph export phase:

Code: Select all

02/14/2008 05:20:14 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT DISTINCT graph_templates_graph.id, graph_templates_graph.local_graph_id, graph_templates_graph.height, graph_templates_graph.width, graph_templates_graph.title_cache, graph_templates.name, graph_local.host_id FROM (graph_tree_items, graph_templates_graph) LEFT JOIN graph_local ON (graph_templates_graph.local_graph_id=graph_local.id) LEFT JOIN graph_templates ON (graph_templates.id=graph_local.graph_template_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_templates_graph.local_graph_id<>0) 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_local.host_id=0) AND (graph_templates_graph.export='on')) ORDER BY graph_templates_graph.title_cache"

02/14/2008 05:20:14 PM - 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=1 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"

Running the first SQL query against the database in phpMyAdmin reuslts in:

#1054 - Unknown column 'host.id' in 'on clause'

And the second SQL query results:

#1054 - Unknown column 'graph_templates_graph.graph_template_id' in 'on clause'

[Edit: mySQL version 5.0.54, Generic Cacti 0.8.7b with no add-ons.]

- Ron -

Please report this bug as given by http://www.cacti.net/bugs.php
Reinhard

helzerr · Post by **helzerr** » Wed Feb 20, 2008 5:39 pm

Since no one else reported experiencing this problem, I decided to re-create my entire Cacti database from the cacti.sql file contained in the 0.8.7b distribution, which resolved the "SQL Assoc Failed!, Error:'1054'" issue. This would indicate to me that some SQL may be missing in the 0.8.7a -> 0.8.7b update.

Resolving the database issue brought back the graph export path problem I initially reported. Reverting lines 152 and 165 to the 0.8.6j code worked around that problem as before, indicating there is still a problem with 'remotedir' / 'path_html_export' variable handling in lib/graph_export.php.

Now, since I have re-created all my data sources, RRAs, and graphs from within 0.8.7b, I am experiencing the "ERROR: the RRD does not contain an RRA matching the chosen CF" problem while creating my WeatehrBug v2.0 graphs.

- Ron -

Nevare · Post by **Nevare** » Tue Mar 04, 2008 9:23 am

I experienced about a gazillion export problems in 0.8.6i (the version included in debian stable), so I upgraded to 0.8.7b (latest stable version). After mucking around with the settings for about 3 hours, I now have the same problem as oh so many others:

Code: Select all

03/04/2008 03:15:04 PM - 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=4) OR (host.id=user_auth_perms.item_id AND user_auth_perms.type=3 AND user_auth_perms.user_id=4) OR (graph_templates.id=user_auth_perms.item_id AND user_auth_perms.type=4 AND user_auth_perms.user_id=4)) WHERE graph_tree_items.graph_tree_id=3 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"

Has anyone managed to get around this? Is this still considered a bug?

When I upgraded, I completely wiped my old installation (the database, the old files, rras, everything) and set it up fresh.

Oh, and the "Export Directory (both local and ftp)" setting says "
[ERROR: DIR NOT FOUND]" unless I set it to something the local system can find (this is with SFTP set as the export method).

kasimong · Post by **kasimong** » Tue Mar 11, 2008 8:00 am

jbiberian · Post by **jbiberian** » Fri Mar 21, 2008 11:21 am

Has someone fixed up the SQL Error 1054 without loosing everything? I experience the same... and need some help

Panther_1 · Post by **Panther_1** » Fri Mar 28, 2008 1:46 pm

I have the same sql error here:

03/28/2008 07:40:14 PM - 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"

Somebody any solution?

There must be somthing wrong with the database on updates. I have create a new database from the cacti.sql the I try to insert teh data from my running database: I got some :

ERROR 1062 (23000): Duplicate entry '1' for key 1

ERROR 1062 (23000): Duplicate entry '2-38-47' for key 1

ans so on.

kasimong · Post by **kasimong** » Mon Apr 14, 2008 6:29 am

hi,
is the problem solved ??????

Wolfgang

04/14/2008 01:25:18 PM - EXPORT STATS: ExportDate:2008-04-14_13:25:18 ExportDuration:0.4096 TotalGraphsExported:0
04/14/2008 01:25:18 PM - CMDPHP: Poller[0] SELECT DISTINCT graph_templates_graph.id, graph_templates_graph.local_graph_id, graph_templates_graph.height, graph_templates_graph.width, graph_templates_graph.title_cache, graph_templates.name, graph_local.host_id FROM (graph_tree_items, graph_templates_graph) LEFT JOIN graph_templates ON ms ON ((graph_templates_graph.local_graph_id=user_auth_perms.item_id and user_auth_perms.type=1 AND user_auth_perms.user_id=18) OR (host.id=user_auth_perms.item_id AND user_auth_perms.type=3 AND user_auth_perms.user_id=18) OR (graph_templates.id=user_auth_perms.item_id AND user_auth_perms.type=4 AND user_auth_perms.user_id=18)) WHERE ((graph_templates_graph.local_graph_id<>0) AND ((user_auth_perms.type = 1 OR user_auth_perms.type is not null) OR ((user_auth_perms.type = 3) OR (user_auth_perms.type is not null)) OR ((user_auth_perms.type = 4) OR (user_auth_perms.type is not null))) AND (graph_local.host_id=0) AND (graph_templates_graph.export='on')) ORDER BY graph_templates_graph.title_cache"

grazzo · Post by **grazzo** » Thu Apr 17, 2008 7:50 am

Hi,

we have the same problem! Is there a fix or a workaround?

Is it possible to use cacti.sql structure provided with 0.8.7b installation files to import data from previous version?

Many thanks

Marco

Cacti

Graph Export to FTP, SQL Assoc Failed!, Error:'1054'

Graph Export to FTP, SQL Assoc Failed!, Error:'1054'

"SQL Assoc Failed!, Error:'1054'" after 0.8.7b upd

Re: "SQL Assoc Failed!, Error:'1054'" after 0.8.7b

New 0.8.7b installation, same problem

Who is online