CDEFs not working

[bbice]

Hiya! I perused the forum, but couldn't find a solution to this (but I DID find the answers
to several other questions - ) I can't use any CDEFs without getting broken graphs.
It doesn't seem to matter which CDEF I use (I've tried the bits to bytes one and another
for converting per/second values to per/5min values).

Here's the PHP error I get when I click on the link that is in the place of the broken
graph, and the command the link shows me:
Warning: Supplied argument is not a valid MySQL result resource in /home.local/httpd/html/cacti-0.6.7/include/rrd_functions.php on line 573

Warning: Supplied argument is not a valid MySQL result resource in /home.local/httpd/html/cacti-0.6.7/include/rrd_functions.php on line 573

/usr/local/rrdtool/bin/rrdtool graph - \
--imgformat=PNG \
--start="-86400" \
--title="Traffic Analysis for omega_traf_216_1_182_44_2" \
--rigid \
--base=1000 \
--height=120 \
--width=500 \
--alt-autoscale-max \
--vertical-label="Bytes Per Second" \
DEF:a="/home.local/httpd/html/cacti/rra/omega_traf_216_1_182_44_2_in.rrd":omega_traf_216_1_18:AVERAGE \
DEF:b="/home.local/httpd/html/cacti/rra/omega_traf_216_1_182_44_2_out.rrd":omega_traf_216_1_18:AVERAGE \
CDEF:cdefa= \
CDEF:cdefe= \
AREA:cdefa#00CF00:"Inbound" \
GPRINTLAST:" Current\:%8.2lf %s" \
GPRINTAVERAGE:"Average\:%8.2lf %s" \
GPRINTMAX:"Maximum\:%8.2lf %s\n" \
LINE1:cdefe#002A97:"Outbound" \
GPRINTLAST:"Current\:%8.2lf %s" \
GPRINTAVERAGE:"Average\:%8.2lf %s" \
GPRINTMAX:"Maximum\:%8.2lf %s"

I assume the problem is with the two CDEF lines above having nothing after the =
and it would imply the reason has to do with the two MySql errors above it, right?
When I run it manually, I see:
ERROR: can't parse CDEF 'cdefa='

I'm running on Mandrake Linux on an Alpha (grin - yeah, I know, but nobody
wanted it), have rrdtool 1.0.37, and PHP 4.1.2 (all compiled by me, no RPMs). Oh
yeah, I'm also running cacti 0.6.7.

I also selected the CDEFs and clicked on the "edit" links and they appear fine.
(shrug) I even tried editing them and each element of the CDEF and clicking on the
Save button in each edit form, but I still get the broken command above.

Any ideas? Everything else is working fine so far, but I suppose I could try
reinstalling everything on a spare Solaris machine. (shrug)

Brent

[raX]

The query that is failing on line 573 is of course the query that gets all of the CDEF data. The only way this query can fail is if cacti is using an invalid CDEF ID (which is unlikely), or there are missing CDEF tables. I am going to guess on the latter.

Also check and make sure you have both the 'rrd_ds_cdef' and 'rrd_ds_cdef_items' tables. If you are not sure, run the following queries:

Code: Select all

#
# Table structure for table `rrd_ds_cdef`
#

CREATE TABLE rrd_ds_cdef (
  ID smallint(5) NOT NULL auto_increment,
  Name varchar(255) NOT NULL default '',
  Type tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (ID),
  UNIQUE KEY ID (ID)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `rrd_ds_cdef_item`
#

CREATE TABLE rrd_ds_cdef_item (
  ID smallint(5) NOT NULL auto_increment,
  DSID smallint(5) default NULL,
  CDEFID smallint(5) default NULL,
  Custom varchar(255) default NULL,
  CurrentDS char(2) default NULL,
  CDEFFunctionID smallint(5) default NULL,
  Type varchar(30) NOT NULL default '0',
  Sequence smallint(5) NOT NULL default '0',
  PRIMARY KEY  (ID),
  UNIQUE KEY ID (ID)
) TYPE=MyISAM;

Your version of MySQL could also be the cultript as well. What version are you using?

Hope this helps.

-Ian

[bbice]

I'm running MySql 3.22.32.

Those two tables already exist. I also did
select * from rrd_ds_cdef_item;

There are rows in that table, so ostensibly they should exist and be usable I'd
Is MySql 3.22.32 too old? I thought I checked it before installing cacti, but I
forgotten...

[bmuha]

I also am having the same problem

mysql> describe rrd_ds_cdef ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| ID | smallint(5) | | PRI | 0 | auto_increment |
| Name | varchar(255) | | | | |
| Type | tinyint(1) | | | 1 | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe rrd_ds_cdef_item ;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| ID | smallint(5) | | PRI | 0 | auto_increment |
| DSID | smallint(5) | YES | | NULL | |
| CDEFID | smallint(5) | YES | | NULL | |
| Custom | varchar(255) | YES | | NULL | |
| CurrentDS | char(2) | YES | | NULL | |
| CDEFFunctionID | smallint(5) | YES | | NULL | |
| Type | varchar(30) | | | 0 | |
| Sequence | smallint(5) | | | 0 | |
+----------------+--------------+------+-----+---------+----------------+

mysql> select * from rrd_ds_cdef_item;
+----+------+--------+--------+-----------+----------------+---------------+----------+
| ID | DSID | CDEFID | Custom | CurrentDS | CDEFFunctionID | Type | Sequence |
+----+------+--------+--------+-----------+----------------+---------------+----------+
| 8 | 54 | 2 | 8 | | 23 | Custom Entry | 2 |
| 7 | 54 | 2 | | on | 31 | Data Source | 1 |
| 9 | 54 | 2 | | | 29 | CDEF Function | 3 |
| 10 | 54 | 4 | | on | 31 | Data Source | 1 |
| 11 | 54 | 4 | 300 | | 31 | Custom Entry | 2 |
| 12 | 54 | 4 | | | 29 | CDEF Function | 3 |
| 13 | 54 | 3 | | on | 31 | Data Source | 1 |
| 14 | 54 | 3 | -1 | | 31 | Custom Entry | 2 |
| 15 | 54 | 3 | | | 29 | CDEF Function | 3 |
| 16 | 0 | 14 | | | 31 | Data Source | 1 |
| 17 | 0 | 14 | 8 | | 23 | Custom Entry | 2 |
| 18 | 0 | 14 | | | 29 | CDEF Function | 3 |
+----+------+--------+--------+-----------+----------------+---------------+----------+
12 rows in set (0.00 sec)

mysql> select * from rrd_ds_cdef;
+----+----------------------------------------------+------+
| ID | Name | Type |
+----+----------------------------------------------+------+
| 2 | Turn Bytes into Bits | 1 |
| 3 | Make Stack Negative | 1 |
| 4 | Make Per 5 Minutes | 1 |
| 7 | Total All Data Sources | 2 |
| 11 | Staggered Total of Data Sources on a Graph | 3 |
| 12 | Average of All Data Sources on a Graph | 4 |
| 13 | Staggered Average of Data Sources on a Graph | 5 |
| 14 | brian | 1 |
+----+----------------------------------------------+------+
8 rows in set (0.00 sec)

[root@snog2 root]# mysqladmin -uXXXX -pXXXXX version
mysqladmin Ver 8.0 Distrib 3.22.32, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty

Server version 3.22.32
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 days 20 hours 48 min 13 sec

Threads: 1 Questions: 565839 Slow queries: 0 Opens: 152 Flush tables: 1 Open tables: 43

Hope all this helps

--Brian

[bmuha]

just a quick update

you need the newer ver of mysql that supports MyISAM and this can be found in version Version 3.23

I upgraded my mysql and all works now please backup your DB 's this upgrade wiped all my DBS out I may have done something wrong here but i really had nothing in there to worry about

So Please dump your DB's before doing this upgrade

--Brian