thold upgraded to 0.4.9, stopped working

[jgrohol]

We just upgraded Cacti from 0.8.7g to 0.8.7i. After that upgrade, and all was working fine, we upgraded some of our plugins. We've upgraded Settings to 0.71 and thold from 0.4.2 to 0.4.9. Since the plugin upgrade, thold isn't working properly. We don't see anything in the cacti logs about it. It appears to have stopped collecting data. When we update/change a thold template, it doesn't propagate those changes down to the individually configure thold's.

I did a list from the thold_data table and noticed that "lasttime" for every entry is 0000-00-00 00:00:00. The last read and oldvalue columns aren't updating as well. What can I check next?

Code: Select all

+----------+---------------------+------------+
| lastread | lasttime            | oldvalue   |
+----------+---------------------+------------+
| 19.4294  | 0000-00-00 00:00:00 | 1851544164 | 
| 38.7572  | 0000-00-00 00:00:00 | 4270448498 | 
+----------+---------------------+------------+

[Lalbee99]

Did you run the upgrade.php script in plugins/thold/extras ?

[jgrohol]

Yes I did. We couldn't even get it upgraded from 0.4.2 to 0.4.9 correctly without running that.

[jgrohol]

Also in the cacti log I'm seeing this:
02/13/2012 09:26:44 AM - SYSTEM THOLD STATS: Time:0.0020 Tholds:0 DownHosts:0

So thold isn't even noticing that there are already tholds created.

[jgrohol]

I GOT IT!

Finally saw in the cacti logs this:

CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT thold_data.id, thold_data.name AS thold_name, thold_data.graph_id, thold_data.percent_ds, thold_data.expression, thold_data.data_type, thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, UNIX_TIMESTAMP(thold_data.lasttime) AS lasttime, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id, data_template_data.rrd_step, data_template_rrd.rrd_maximum FROM thold_data LEFT JOIN data_template_rrd ON (data_template_rrd.id = thold_data.data_id) LEFT JOIN data_template_data ON ( data_template_data.local_data_id = thold_data.rra_id ) WHERE data_template_rrd.data_source_name!='' AND thold_data.rra_id IN(2121)"

I checked the thold_data table and compared it with the "database.php" file in the thold/includes directory. I noticed that the 'expression' column was missing. I altered the table to add that column and now thold data is collecting once again. I am going to monitor for a few more hours to make sure it's working correctly.

Now the next question.... Did I have that 'expression' column before the upgrade? Or did it not get added in the upgrade script?

[jgrohol]

Welp, thought i had thold running, but it isn't 100% there yet. It is working as far as templates and creating tholds, but now none of them are triggered for warning or alerts. They don't change colors or do anything. I am even testing with one that uses exact value from the SNMP read. Its current value is around 242 (VPN Connections) and we have the trigger alert set to 180. It's pulling the correct value, just not triggering or noticing that it's higher than our alert we've set.

This is actually after a fresh install of the thold plugin. We removed all tholds and thold templates. Re-installed the thold plugin and started some templates and tholds from scratch.

Ideas?

[Lalbee99]

So when I upgraded from THOLD 0.4.3 ---> 0.4.9 I noticed after the upgrade the following missing fields in my thold data table:
==============================================
thold_warning_hi:
==============================================
thold_warning_low:
==============================================
thold_warning_fail_trigger:
==============================================
thold_warning_fail_count:
==============================================
time_warning_hi:
==============================================
time_warning_low:
==============================================
time_warning_fail_trigger:
==============================================
time_warning_fail_length:
==============================================
lasttime:
==============================================
notify_warning_extra:
==============================================
notify_warning:
==============================================
notify_alert:
==============================================
==============================================
expression:
==============================================

I ended up writing a script to dump my existing data from the THOLD 0.4.3. I then add the missing fields then inserted the data into the upgraded 0.4.9 table. I was back in business after that. You will also notice that the thold_template table has missing fileds after the upgrade.

[the_sphynx]

So when I upgraded from THOLD 0.4.3 ---> 0.4.9 I noticed after the upgrade the following missing fields in my thold data table:
==============================================
thold_warning_hi:
==============================================
thold_warning_low:
==============================================
thold_warning_fail_trigger:
==============================================
thold_warning_fail_count:
==============================================
time_warning_hi:
==============================================
time_warning_low:
==============================================
time_warning_fail_trigger:
==============================================
time_warning_fail_length:
==============================================
lasttime:
==============================================
notify_warning_extra:
==============================================
notify_warning:
==============================================
notify_alert:
==============================================
==============================================
expression:
==============================================

I ended up writing a script to dump my existing data from the THOLD 0.4.3. I then add the missing fields then inserted the data into the upgraded 0.4.9 table. I was back in business after that. You will also notice that the thold_template table has missing fileds after the upgrade.

So you say you created a script for this? Is it possible to upload the script or at least explain how you added those fields to the proper table? I am also having the same problem. Although, I noticed it when I was trying to update a threshold template and it was failing with the following error:

CMDPHP: Poller[0] ERROR: SQL Save Command Failed for Table 'thold_template'. Error was 'Unknown column 'expression' in 'field list''

[the_sphynx]

I was able to solve it by just adding the expression column to my thold_template and thold_data tables. It would be nice if there were a sql script included in the plugin so that we can at least see what the structure is supposed to look like. The structure in the database.php script is confusing. Thing appear to be working fine thus far.

[Lalbee99]

THOLD 0.4.9 structure:
id int(11) NOT NULL auto_increment,
name varchar(150) default NULL,
rra_id int(11) NOT NULL default '0',
data_id int(11) NOT NULL default '0',
graph_id int(11) NOT NULL default '0',
graph_template int(11) NOT NULL default '0',
data_template int(11) NOT NULL default '0',
thold_hi varchar(100) default NULL,
thold_low varchar(100) default NULL,
thold_fail_trigger int(10) unsigned default NULL,
thold_fail_count int(11) NOT NULL default '0',
time_hi varchar(100) default NULL,
time_low varchar(100) default NULL,
time_fail_trigger int(12) NOT NULL default '1',
time_fail_length int(12) NOT NULL default '1',
thold_warning_hi varchar(100) default NULL,
thold_warning_low varchar(100) default NULL,
thold_warning_fail_trigger int(10) unsigned default NULL,
thold_warning_fail_count int(11) NOT NULL default '0',
time_warning_hi varchar(100) default NULL,
time_warning_low varchar(100) default NULL,
time_warning_fail_trigger int(12) NOT NULL default '1',
time_warning_fail_length int(12) NOT NULL default '1',
thold_alert int(1) NOT NULL default '0',
thold_enabled enum('on','off') NOT NULL default 'on',
thold_type int(3) NOT NULL default '0',
bl_ref_time_range int(10) unsigned default NULL,
bl_pct_down varchar(100) default NULL,
bl_pct_up varchar(100) default NULL,
bl_fail_trigger int(10) unsigned default NULL,
bl_fail_count int(11) unsigned default NULL,
bl_alert int(2) NOT NULL default '0',
lastread varchar(100) default NULL,
lasttime timestamp NOT NULL default '0000-00-00 00:00:00',
oldvalue varchar(100) default NULL,
repeat_alert int(10) unsigned default NULL,
notify_default enum('on','off') default NULL,
notify_extra text,
notify_warning_extra varchar(512) default NULL,
notify_warning int(10) unsigned default NULL,
notify_alert int(10) unsigned default NULL,
host_id int(10) default NULL,
syslog_priority int(2) NOT NULL default '3',
data_type int(12) NOT NULL default '0',
cdef int(11) NOT NULL default '0',
percent_ds varchar(64) NOT NULL default '',
expression varchar(70) NOT NULL default '',
template int(11) NOT NULL default '0',
template_enabled char(3) NOT NULL default '',
tcheck int(1) NOT NULL default '0',
exempt char(3) NOT NULL default 'off',
restored_alert char(3) NOT NULL default 'off',
bl_thold_valid int(10) unsigned NOT NULL default '0',


Thold 0.4.3 Structure:
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
rra_id int(11) NOT NULL default '0',
data_id int(11) NOT NULL default '0',
graph_id int(11) NOT NULL default '0',
graph_template int(11) NOT NULL default '0',
data_template int(11) NOT NULL default '0',
thold_hi varchar(100) default NULL,
thold_low varchar(100) default NULL,
thold_fail_trigger int(10) unsigned default NULL,
time_hi varchar(100) default NULL,
time_low varchar(100) default NULL,
time_fail_trigger int(12) NOT NULL default '1',
time_fail_length int(12) NOT NULL default '1',
thold_fail_count int(11) NOT NULL default '0',
thold_alert int(1) NOT NULL default '0',
thold_enabled enum('on','off') NOT NULL default 'on',
thold_type int(3) NOT NULL default '0',
bl_enabled enum('on','off') NOT NULL default 'off',
bl_ref_time int(50) unsigned default NULL,
bl_ref_time_range int(10) unsigned default NULL,
bl_pct_down int(10) unsigned default NULL,
bl_pct_up int(10) unsigned default NULL,
bl_fail_trigger int(10) unsigned default NULL,
bl_fail_count int(11) unsigned default NULL,
bl_alert int(2) NOT NULL default '0',
lastread varchar(100) default NULL,
oldvalue varchar(100) NOT NULL default '',
repeat_alert int(10) unsigned default NULL,
notify_extra varchar(255) default NULL,
host_id int(10) default NULL,
syslog_priority int(2) default '3',
data_type int(3) NOT NULL default '0',
cdef int(11) NOT NULL default '0',
percent_ds varchar(64) NOT NULL,
template int(11) NOT NULL default '0',
template_enabled char(3) NOT NULL default '',
tcheck int(1) NOT NULL default '0',
exempt char(3) NOT NULL default 'off',
restored_alert char(3) NOT NULL default 'off',

[Lalbee99]

1) I used the following perl script to output my 0.4.3 data # delimited.

#!/usr/bin/perl
sub GET_THOLD {
use DBI;
$output_file="${TMP_DIR}thold_043_data";
open OUT2, ">>$output_file";
my $dsn = "DBI:mysql:${MYSQL_CACTI_DB}:localhost";
my $db_user_name = "${MYSQL_CACTI_USER}";
my $db_password = "${MYSQL_CACTI_PASSWORD}";
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
my $sql = qq{ SELECT id,'#',name,'#',rra_id,'#',data_id,'#',graph_id,'#',graph_template,'#',data_template,'#',thold_hi,'#',thold_low,'#',thold_fail_trigger,'#',time_hi,'#',time_low,'#',time_fail_trigger,'#',time_fail_length,'#',thold_fail_count,'#',thold_alert,'#',thold_enabled,'#',thold_type,'#',bl_enabled,'#',bl_ref_time,'#',bl_ref_time_range,'#',bl_pct_down,'#',bl_pct_up,'#',bl_fail_trigger,'#',bl_fail_count,'#',bl_alert,'#',lastread,'#',oldvalue,'#',repeat_alert,'#',notify_extra,'#',host_id,'#',syslog_priority,'#',data_type,'#',cdef,'#',percent_ds,'#',template,'#',template_enabled,'#',tcheck,'#',exempt,'#',restored_alert from thold_data;};
my $sth = $dbh->prepare( $sql );
$sth->execute();

while (my(@row) = $sth->fetchrow_array)
{
print OUT2 " @row \n";
}
$sth->finish();
$dbh->disconnect();
close(OUT2) ;
}
####################################
# USER CONFIGURABLE
####################################
$MYSQL_CACTI_USER="XXXX";
$MYSQL_CACTI_PASSWORD="XXXXX";
chomp($MYSQL_CACTI_DB="cacti_087i");
$PID = getppid ;
$TMP_DIR="/tmp/" ;
###############################################
&GET_THOLD;

2) I then uninstalled my 0.4.3 thold plugin and then installed the 0.4.9 thold plugin

3) I then ran the following ksh script to produce a sql file that inserts my old data into the new 0.4.9 thold_data table.

#!/usr/bin/ksh
while read a ;
do
id="`echo $a | awk -F# '{ print $1 }'| sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
name="`echo $a | awk -F# '{ print $2 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
rra_id="`echo $a | awk -F# '{ print $3 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
data_id="`echo $a | awk -F# '{ print $4 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
graph_id="`echo $a | awk -F# '{ print $5 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
graph_template="`echo $a | awk -F# '{ print $6 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
data_template="`echo $a | awk -F# '{ print $7 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_hi="`echo $a | awk -F# '{ print $8 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_low="`echo $a | awk -F# '{ print $9 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_fail_trigger="`echo $a | awk -F# '{ print $10 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_fail_count=0
time_hi=""
time_low=""
time_fail_trigger="`echo $a | awk -F# '{ print $13 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
time_fail_length="`echo $a | awk -F# '{ print $14 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_warning_hi=""
thold_warning_low=""
thold_warning_fail_trigger="''"
thold_warning_fail_count=0
time_warning_hi=""
time_warning_low=""
time_warning_fail_trigger=1
time_warning_fail_length=1
thold_alert="`echo $a | awk -F# '{ print $16 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_enabled="`echo $a | awk -F# '{ print $17 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
thold_type="`echo $a | awk -F# '{ print $18 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
bl_ref_time_range=""
bl_pct_down=""
bl_pct_up=""
bl_fail_trigger=3
bl_fail_count=""
bl_alert=""
lastread="`echo $a | awk -F# '{ print $27 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
lasttime="0000-00-00 00:00:00"
oldvalue="`echo $a | awk -F# '{ print $28 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
repeat_alert="`echo $a | awk -F# '{ print $29 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
notify_default=""
notify_extra="`echo $a | awk -F# '{ print $30 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
notify_warning=""
notify_warning_extra=""
notify_alert=""
host_id="`echo $a | awk -F# '{ print $31 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
syslog_priority="`echo $a | awk -F# '{ print $32 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
data_type="`echo $a | awk -F# '{ print $33 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
cdef="`echo $a | awk -F# '{ print $34 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
percent_ds="`echo $a | awk -F# '{ print $35 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
expression=""
template="`echo $a | awk -F# '{ print $36 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
template_enabled="`echo $a | awk -F# '{ print $37 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
tcheck="`echo $a | awk -F# '{ print $38 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
exempt="`echo $a | awk -F# '{ print $39 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
restored_alert="`echo $a | awk -F# '{ print $40 }' | sed -e 's/^[ \t]*//' | sed 's/[ \t]*$//'`"
bl_thold_valid=0
echo " INSERT INTO \`thold_data\` VALUES (${id},'${name}','${rra_id}','${data_id}','${graph_id}','${graph_template}','${data_template}','${thold_hi}','${thold_low}','${thold_fail_trigger}','${thold_fail_count}','${time_hi}','${time_low}','${time_fail_trigger}','${time_fail_length}','${thold_warning_hi}','${thold_warning_low}','${thold_warning_fail_trigger}','${thold_warning_fail_count}','${time_warning_hi}','${time_warning_low}','${time_warning_fail_trigger}','${time_warning_fail_length}','${thold_alert}','${thold_enabled}','${thold_type}','${bl_ref_time_range}','${bl_pct_down}','${bl_pct_up}','${bl_fail_trigger}','${bl_fail_count}','${bl_alert}','${lastread}','${lasttime}','${oldvalue}','${repeat_alert}','${notify_default}','${notify_extra}','${notify_warning_extra}','${notify_warning}','${notify_alert}','${host_id}','${syslog_priority}','${data_type}','${cdef}','${percent_ds}','${expression}','${template}','${template_enabled}','${tcheck}','${exempt}','${restored_alert}','${bl_thold_valid}');"
done < thold_043_data

[computer_guru]

I had a problem with thold 0.4.9 after upgrading to cacti 0.8.7i too. I fixed it myself by adding the column "expression" to the thold_data table in mysql

[dalpha]

I was attempting to update thold from 0.4.1 to 0.4.9 and ran into this same issue. I tried running the update.php script, however, that did not fully update mysql. I needed to manually run these commands to get 0.4.9 fully-functioning:

Code: Select all

ALTER TABLE thold_data DROP COLUMN bl_enabled;
ALTER TABLE thold_template DROP COLUMN bl_enabled;
ALTER TABLE thold_data ADD COLUMN expression varchar(70) NOT NULL DEFAULT '';
ALTER TABLE thold_template ADD COLUMN expression varchar(70) NOT NULL DEFAULT '';