Manage-error: sql error and gone service/type links?

[torstentfk]

Hi,

upgraded from veriosn 0.52 to 0.61 (incl. pia 2.4):
- link to "Ddevice Types" is gone. where can i find it?
- after upgrade all devices have no image at the device page - all gone.
-> going to device-page, select all devices -> action "manage" and select only a image to change- nothing changes at the device page. Changing manualy one device displays also no image (only the selected image on the device page changed).
- No link to the service page ( where I can define tcp -services to manage)
- I get errors in the cacti log:

Code: Select all

03/06/2009 05:43:18 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1242', SQL:"select data_input_data.value, data_local.snmp_index from data_input_data, data_template_data, data_local, data_template where data_input_data.data_input_field_id=(SELECT distinct(data_input_fields.id) FROM data_input, data_input_fields where data_input.type_id = 6 and data_input_fields.data_input_id=data_input.id and data_input_fields.name='Index Value') and data_input_data.data_template_data_id=data_template_data.id and data_template_data.local_data_id=data_local.id and data_template_data.data_template_id=data_template.id and data_template.name='Win Services' and data_local.host_id='142'"
03/06/2009 05:43:18 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1242', SQL:"select data_input_data.value, data_local.snmp_index from data_input_data, data_template_data, data_local, data_template where data_input_data.data_input_field_id=(SELECT distinct(data_input_fields.id) FROM data_input, data_input_fields where data_input.type_id = 6 and data_input_fields.data_input_id=data_input.id and data_input_fields.name='Index Value') and data_input_data.data_template_data_id=data_template_data.id and data_template_data.local_data_id=data_local.id and data_template_data.data_template_id=data_template.id and data_template.name='Win Services' and data_local.host_id='140'"

-> I have no service "win Service"; and if I create per hand a data-query winservice it does not help.

Installed plugins: Thold, setting, boost,mactrack,uptime,syslog
Using debian system, php5,msql5.

- What must I do to get manage working?
- Is there a possibility to set default options for all users for the display-settings at manage-tab?

Torsten

ps: the debug-output of"check tables"

Code: Select all

Current version : 0.6.1
--------------------------------------------------------------------------------
Upgrade needed : none
--------------------------------------------------------------------------------
Table 'manage_host' : 	OK	(0.1)
Column 'manage' from table 'host' : 	OK	(0.1)
Table 'manage_alerts' : 	OK	(0.1)
Column 'force' from table 'manage_host' : 	NOK	(0.2/0.5.2)
Table 'manage_templates' : 	OK	(0.2)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'group' from table 'manage_host' : 	OK	(0.3)
Table 'manage_groups' : 	OK	(0.3)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'oid' from table 'manage_alerts' : 	OK	(0.4)
Table 'manage_process' : 	OK	(0.4)
Table 'manage_services' : 	OK	(0.4)
Setting 'manage_poller_hosts' from table 'settings' : 	OK	(0.4)
--------------------------------------------------------------------------------------------------------------------------------------------
Table 'manage_sites' : 	OK	(0.5)
Table 'manage_tcp' : 	OK	(0.5)
Table 'manage_host_services' : 	NOK	(0.1/0.5)
Table 'manage_poller_output' : 	OK	(0.5)
Column 'thresold_ref' from table 'manage_host' : 	OK	(0.5)
Column 'thresold' from table 'manage_host' : 	OK	(0.5)
Column 'mail' from table 'manage_host' : 	OK	(0.5)
Column 'site_id' from table 'manage_groups' : 	OK	(0.5)
Setting 'manage_global_email' from table 'settings' : 	OK	(0.5)
Setting 'manage_double_email' from table 'settings' : 	OK	(0.5)
Setting 'manage_poller_plus' from table 'settings' : 	OK	(0.5)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'type' from table 'manage_host' : 	NOK	(0.5.2)
Column 'services' from table 'manage_host' : 	NOK	(???/0.5.2)
Setting 'manage_accounts_tab' from table 'settings' : 	OK	(0.5.2)
Setting 'manage_accounts_settings' from table 'settings' : 	OK	(0.5.2)
Setting 'manage_accounts_reporting' from table 'settings' : 	OK	(0.5.2)
Setting 'manage_accounts_sites' from table 'settings' : 	OK	(0.5.2)
Setting 'manage_accounts_groups' from table 'settings' : 	OK	(0.5.2)
Table 'manage_device_type' : 	NOK	(0.2/0.5.2)
Setting 'manage_enable' from table 'settings' : 	OK	(0.5.2)
--------------------------------------------------------------------------------------------------------------------------------------------
Table 'manage_uptime_method' : 	OK	(0.6)
Table 'manage_admin_link' : 	OK	(0.6)
Setting 'manage_uptime_method' from table 'settings' : 	OK	(0.6)
Setting 'manage_uptime_cisco' from table 'settings' : 	OK	(0.6)
--------------------------------------------------------------------------------------------------------------------------------------------
Setting 'Use Windows PHP Patch' : 	OK	Don't use. Select 'No'.
Setting 'Global e-mail to sending alerts' : 	NOK	Put an email address.
Plugin Settings : 	OK	
Plugin Thold : 	NOK	Install Plugin Thold.
Concurrent Manage Pollers : 	OK	
Devices : 	OK	
User settings : 	OK	(26)

[TheWitness]

Definately a manage bug (pretty sure anyway).

TheWitness

[gilles]

torstentfk,

debug output shows you have 5 problems :

Code: Select all

Column 'force' from table 'manage_host' :    NOK   (0.2/0.5.2) 
Table 'manage_host_services' :    NOK   (0.1/0.5) 
Column 'type' from table 'manage_host' :    NOK   (0.5.2) 
Column 'services' from table 'manage_host' :    NOK   (???/0.5.2) 
Table 'manage_device_type' :    NOK   (0.2/0.5.2) 

(i can't understand how v0.5.2 or v0.5 was working)
try this (from the sql scripts in sql folder) :

Code: Select all

ALTER TABLE `manage_host` DROP `force`;
ALTER TABLE manage_host_services RENAME manage_tcp;
ALTER TABLE `manage_host` change `type` `type` varchar(255);
ALTER TABLE `manage_host` DROP `services`;
drop TABLE `manage_device_type`;

install plugins thold and and define a default alert email

then see if debug output shows no error

[torstentfk]

Hi,

ok tables dropped and email entered.
thold IS! insalled v0.4.1 (perhaps some change must be done to detect the actual version). Therefore the debug is not clean until the THOLD.

Next problem:
At cacti-log I get:

Code: Select all

 data_template.name='Win Services' and data_local.host_id='35'"
03/10/2009 10:51:42 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1242', SQL:"select data_input_data.value, data_local.snmp_index from data_input_data, data_template_data, data_local, data_template where data_input_data.data_input_field_id=(SELECT distinct(data_input_fields.id) FROM data_input, data_input_fields where data_input.type_id = 6 and data_input_fields.data_input_id=data_input.id and data_input_fields.name='Index Value') and data_input_data.data_template_data_id=data_template_data.id and data_template_data.local_data_id=data_local.id and data_template_data.data_template_id=data_template.id and data_template.name='Win Services' and data_local.host_id='38'"

Template Win Service is not used!


for the default-settings for all users I changed manage_settings.php so that all users will have a default setting and not " is not used"

Code: Select all

  $manage_size=manage_load_default("manage_size", $user_id, "88");
  $manage_thold=manage_load_default("manage_thold", $user_id, "1");
  $manage_list=manage_load_default("manage_list", $user_id, "1");
  $manage_list_2=manage_load_default("manage_list_2", $user_id, "2");
  $manage_expand=manage_load_default("manage_expand", $user_id, "2");
  $manage_theme=manage_load_default("manage_theme", $user_id, "cacti");
  $manage_sound=manage_load_default("manage_sound", $user_id, "on");
  $manage_list_separator=manage_load_default("manage_list_separator", $user_id, "25");
  $manage_full_separator=manage_load_default("manage_full_separator", $user_id, "6");
  $manage_simple_separator=manage_load_default("manage_simple_separator", $user_id, "12");
  $manage_site_separator=manage_load_default("manage_site_separator", $user_id, "10");
  $manage_tree_separator=manage_load_default("manage_tree_separator", $user_id, "3");
  $manage_legend=manage_load_default("manage_legend", $user_id, "on");
  $manage_cycle_delay=manage_load_default("manage_cycle_delay", $user_id, "60");
  $manage_cycle_refresh=manage_load_default("manage_cycle_refresh", $user_id, "10");
  $manage_order1=manage_load_default("manage_order1", $user_id, "5");
  $manage_order2=manage_load_default("manage_order2", $user_id, "1");
  $manage_order3=manage_load_default("manage_order3", $user_id, "2");
  $manage_order4=manage_load_default("manage_order4", $user_id, "3");
  $manage_order5=manage_load_default("manage_order5", $user_id, "6");
  $manage_link_method=manage_load_default("manage_link_method", $user_id, "999");
  $manage_connect_timeout=manage_load_default("manage_connect_timeout", $user_id, "Is not set");
  $manage_relay_ip=manage_load_default("manage_relay_ip", $user_id, "Is not set");
  $manage_relay_port=manage_load_default("manage_relay_port", $user_id, "Is not set");
  $manage_motd_enabled=manage_load_default("manage_motd_enabled", $user_id, "999");
  $manage_motd_style=manage_load_default("manage_motd_style", $user_id, "999");

[gilles]

install win services template, this will remove errors in cacti log

are you using host-info plugin ?
is thold listed ?

you must include thold in your global.php file

Code: Select all

$plugins[] = 'thold';

[torstentfk]

install win services template, this will remove errors in cacti log

are you using host-info plugin ?
is thold listed ?

you must include thold in your global.php file

Code: Select all

$plugins[] = 'thold';

- I made a template by my one - did not help.
- deleted and imported the template from this board -> no change
- I have the new thold installed with PIA 2.x support. So I do not need to but it into global.php (or am I wrong?)

Torsten

[torstentfk]

the sql-command that causes everytime the error is:

Code: Select all

 SELECT data_input_data.value, data_local.snmp_index
FROM data_input_data, data_template_data, data_local, data_template
WHERE data_input_data.data_input_field_id = (
SELECT DISTINCT (
data_input_fields.id
)
FROM data_input, data_input_fields
WHERE data_input.type_id =6
AND data_input_fields.data_input_id = data_input.id
AND data_input_fields.name = 'Index Value' )
AND data_input_data.data_template_data_id = data_template_data.id
AND data_template_data.local_data_id = data_local.id
AND data_template_data.data_template_id = data_template.id
AND data_template.name = 'Win Services'
AND data_local.host_id = '57'
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1242 - Subquery returns more than 1 row 

id=6 is hard coded and means "get script server indexed"; host is variable.
The second WHERE-clause isthe problem - as mysql said.

the distinct-clause returns 2 results: in my case 36 and 90.

[dorojatunb]

torstentfk,



debug output shows you have 5 problems :

Code: Select all

Column 'force' from table 'manage_host' :    NOK   (0.2/0.5.2) 
Table 'manage_host_services' :    NOK   (0.1/0.5) 
Column 'type' from table 'manage_host' :    NOK   (0.5.2) 
Column 'services' from table 'manage_host' :    NOK   (???/0.5.2) 
Table 'manage_device_type' :    NOK   (0.2/0.5.2) 

(i can't understand how v0.5.2 or v0.5 was working)
try this (from the sql scripts in sql folder) :

Code: Select all

ALTER TABLE `manage_host` DROP `force`;
ALTER TABLE manage_host_services RENAME manage_tcp;
ALTER TABLE `manage_host` change `type` `type` varchar(255);
ALTER TABLE `manage_host` DROP `services`;
drop TABLE `manage_device_type`;

install plugins thold and and define a default alert email



then see if debug output shows no error

This is my debug:
Current version : 0.6.2
--------------------------------------------------------------------------------
Upgrade needed : none
--------------------------------------------------------------------------------
Table 'manage_host' : OK (0.1)
Column 'manage' from table 'host' : OK (0.1)
Table 'manage_alerts' : OK (0.1)
Column 'force' from table 'manage_host' : OK (not here) (0.2/0.5.2)
Table 'manage_templates' : OK (0.2)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'group' from table 'manage_host' : NOK (0.3)
Table 'manage_groups' : OK (0.3)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'oid' from table 'manage_alerts' : OK (0.4)
Table 'manage_process' : OK (0.4)
Table 'manage_services' : OK (0.4)
Setting 'manage_poller_hosts' from table 'settings' : OK (0.4)
--------------------------------------------------------------------------------------------------------------------------------------------
Table 'manage_sites' : OK (0.5)
Table 'manage_tcp' : OK (0.5)
Table 'manage_host_services' : NOK (0.1/0.5)
Table 'manage_poller_output' : OK (0.5)
Column 'thresold_ref' from table 'manage_host' : NOK (0.5)
Column 'thresold' from table 'manage_host' : NOK (0.5)
Column 'mail' from table 'manage_host' : NOK (0.5)
Column 'site_id' from table 'manage_groups' : OK (0.5)
Setting 'manage_global_email' from table 'settings' : OK (0.5)
Setting 'manage_double_email' from table 'settings' : OK (0.5)
Setting 'manage_poller_plus' from table 'settings' : OK (0.5)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'type' from table 'manage_host' : NOK (0.5.2)
Column 'services' from table 'manage_host' : NOK (???/0.5.2)
Setting 'manage_accounts_tab' from table 'settings' : OK (0.5.2)
Setting 'manage_accounts_settings' from table 'settings' : OK (0.5.2)
Setting 'manage_accounts_reporting' from table 'settings' : OK (0.5.2)
Setting 'manage_accounts_sites' from table 'settings' : OK (0.5.2)
Setting 'manage_accounts_groups' from table 'settings' : OK (0.5.2)
Table 'manage_device_type' : OK (not here) (0.2/0.5.2)
Setting 'manage_enable' from table 'settings' : OK (0.5.2)
--------------------------------------------------------------------------------------------------------------------------------------------
Table 'manage_uptime_method' : OK (0.6)
Table 'manage_admin_link' : OK (0.6)
Setting 'manage_uptime_method' from table 'settings' : OK (0.6)
Setting 'manage_uptime_cisco' from table 'settings' : OK (0.6)
--------------------------------------------------------------------------------------------------------------------------------------------
Setting 'Use Windows PHP Patch' : OK Don't use. Select 'No'.
Plugin Thold : OK
Concurrent Manage Pollers : OK
devices : NOK These hosts need to be associated to a site and a group :
User settings : NOK Save your settings (0).

Help me to solve the NOK value please.
Thanks

[dimm]

Hello guys.
I have the same problem.

Cacti version 0.8.7g
Manage version 0.6.2
Weathermap version 0.97a

There is message form cacti.log:

Code: Select all

01/27/2012 08:31:14 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1242', SQL:"select data_input_data.value, data_local.snmp_index from data_input_data, data_template_data, data_local, data_template where data_input_data.data_input_field_id=(SELECT distinct(data_input_fields.id) FROM data_input, data_input_fields where data_input.type_id = 6 and data_input_fields.data_input_id=data_inp
ut.id and data_input_fields.name='Index Value') and data_input_data.data_template_data_id=data_template_data.id and data_template_data.local_data_id=data_local.id and data_template_data.data_template_id=data_template.id and data_template.name='Win Services' and data_local.host_id='483'"

manage_debug:

Code: Select all

Current version : 0.6.2
--------------------------------------------------------------------------------
Upgrade needed : none
--------------------------------------------------------------------------------
Table 'manage_host' :	OK	(0.1)
Column 'manage' from table 'host' :	OK	(0.1)
Table 'manage_alerts' :	OK	(0.1)
Column 'force' from table 'manage_host' :	OK (not here)	(0.2/0.5.2)
Table 'manage_templates' :	OK	(0.2)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'group' from table 'manage_host' :	OK	(0.3)
Table 'manage_groups' :	OK	(0.3)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'oid' from table 'manage_alerts' :	OK	(0.4)
Table 'manage_process' :	OK	(0.4)
Table 'manage_services' :	OK	(0.4)
Setting 'manage_poller_hosts' from table 'settings' :	OK	(0.4)
--------------------------------------------------------------------------------------------------------------------------------------------
Table 'manage_sites' :	OK	(0.5)
Table 'manage_tcp' :	OK	(0.5)
Table 'manage_host_services' :	OK (not here)	(0.1/0.5)
Table 'manage_poller_output' :	OK	(0.5)
Column 'thresold_ref' from table 'manage_host' :	OK	(0.5)
Column 'thresold' from table 'manage_host' :	OK	(0.5)
Column 'mail' from table 'manage_host' :	OK	(0.5)
Column 'site_id' from table 'manage_groups' :	OK	(0.5)
Setting 'manage_global_email' from table 'settings' :	OK	(0.5)
Setting 'manage_double_email' from table 'settings' :	OK	(0.5)
Setting 'manage_poller_plus' from table 'settings' :	OK	(0.5)
--------------------------------------------------------------------------------------------------------------------------------------------
Column 'type' from table 'manage_host' :	OK	(0.5.2)
Column 'services' from table 'manage_host' :	OK (not here)	(???/0.5.2)
Setting 'manage_accounts_tab' from table 'settings' :	OK	(0.5.2)
Setting 'manage_accounts_settings' from table 'settings' :	OK	(0.5.2)
Setting 'manage_accounts_reporting' from table 'settings' :	OK	(0.5.2)
Setting 'manage_accounts_sites' from table 'settings' :	OK	(0.5.2)
Setting 'manage_accounts_groups' from table 'settings' :	OK	(0.5.2)
Table 'manage_device_type' :	OK (not here)	(0.2/0.5.2)
Setting 'manage_enable' from table 'settings' :	OK	(0.5.2)
--------------------------------------------------------------------------------------------------------------------------------------------
Table 'manage_uptime_method' :	OK	(0.6)
Table 'manage_admin_link' :	OK	(0.6)
Setting 'manage_uptime_method' from table 'settings' :	OK	(0.6)
Setting 'manage_uptime_cisco' from table 'settings' :	OK	(0.6)
--------------------------------------------------------------------------------------------------------------------------------------------
Setting 'Use Windows PHP Patch' :	OK	Don't use. Select 'No'.
Plugin Thold :	OK	
Concurrent Manage Pollers :	OK	
Devices :	OK	
User settings :	NOK	Save your settings (0).

Please help me to fix this error message.

[gandalf]

Did you google the error message?
Which mysql version are you running?
R.

[dimm]

Did you google the error message?
Which mysql version are you running?
R.

Yes, I try to google it and find this topic only.
mysql-5.0.77-4.el5_4.2

[gandalf]

I meant to google the mysql error message. That one shows an issue with a subquery, that is version dependant. It might be an issue with manage as well. In this case, please contact plugin author
R.