[rrd-users] rrd dbi syntx

[John Stile]

I am having a hard time finding the syntax for using a dbi with rrdtool
graph.

Command:
rrdtool graph ./test.png --imgformat=PNG --start=-1day --end=+0hours --width=1000 --height=600 "DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/date/cpu0:min:AVERAGE" "LINE1:min#FF0000:cpu0"

Error:
ERROR: formatstring wrong - mysql


Table: echo 'describe temperature' | mysql -u'thermal' -p'thermal' -D
'thermal'

Field Type Null Key Default Extra
date timestamp NO PRI CURRENT_TIMESTAMP
cpuPhy int(11) NO 0
cpu0 int(11) NO 0
cpu1 int(11) NO 0
cpu2 int(11) NO 0
cpu3 int(11) NO 0
disk0 int(11) NO 0
disk1 int(11) NO 0
disk2 int(11) NO 0
disk3 int(11) NO 0
board0 int(11) NO 0
board1 int(11) NO 0

The column 'date' is of type timestamp.
I am using gentoo Linux with rrdtool-1.4.8 and mysql-5.1.70.

eix rrdtool
[I] net-analyzer/rrdtool
Available versions: 1.4.7-r1 (~)1.4.7-r2 1.4.8 {dbi doc +graph lua perl python rrdcgi ruby static-libs tcl tcpd PYTHON_TARGETS="python2_7"}
Installed versions: 1.4.8(03:07:05 PM 03/30/2014)(dbi graph perl python rrdcgi static-libs tcpd -doc -lua -ruby -tcl PYTHON_TARGETS="python2_7")
Homepage: http://oss.oetiker.ch/rrdtool/
Description: A system to store and display time-series data

eix -I mysql
[I] dev-db/mysql
Available versions: [M]4.0.27-r1 [M]4.1.22-r1 [M]5.0.96 5.1.70 ~5.5.32 {berkdb big-tables cluster +community debug embedded extraengine jemalloc latin1 max-idx-128 minimal pbxt (+)perl profiling raid selinux ssl static systemtap tcmalloc test xtradb}
Installed versions: 5.1.70(09:15:54 PM 03/22/2014)(community perl ssl -big-tables -cluster -debug -embedded -extraengine -latin1 -max-idx-128 -minimal -pbxt -profiling -selinux -static -test -xtradb)
Homepage: http://www.mysql.com/
Description: A fast, multi-threaded, multi-user SQL database server.



_______________________________________________
rrd-users mailing list
rrd-...@lists.oetiker.ch
https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users

[John Stile]

On Mon, 2014-03-31 at 04:00 +0000, Steve Shipway wrote:
> >Command:
> >rrdtool graph ./test.png --imgformat=PNG --start=-1day --end=+0hours
> --width=1000 --height=600
> >"DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password
> =thermal//temperature/date/cpu0:min:AVERAGE" "LINE1:min#FF0000:cpu0"
> >
> >Error:
> >ERROR: formatstring wrong - mysql
>

> Check that you have the packages libdbi, libdbi-dbd-mysql and libdbi-drivers
> installed. It might be that you have libdbi but not the mysql driver, so
> the format would be wrong.
>
> Steve
>
> Steve Shipway
> s.sh...@auckland.ac.nz
>

I do have the mysql driver for libdbi installed

[I] dev-db/libdbi
Available versions: 0.8.3 0.8.4 0.9.0 {doc static-libs}
Installed versions: 0.9.0(03:06:28 PM 03/30/2014)(static-libs -doc)
Homepage: http://libdbi.sourceforge.net/
Description: libdbi is a database-independent abstraction layer in C, similar to the DBI/DBD layer in Perl.

[I] dev-db/libdbi-drivers
Available versions: 0.8.3 0.8.3-r2 0.9.0 {bindist doc firebird mysql oci8 postgres (+)sqlite sqlite3 static-libs}
Installed versions: 0.9.0(03:06:40 PM 03/30/2014)(mysql sqlite static-libs -bindist -doc -firebird -oci8 -postgres)
Homepage: http://libdbi-drivers.sourceforge.net/
Description: The libdbi-drivers project maintains drivers for libdbi.

[John Stile]

On Mon, 2014-03-31 at 21:19 +0000, Steve Shipway wrote:
> >rrdtool graph
> >./test.png --imgformat=PNG --start=-1day --end=+0hours --width=1000 --height=600

> >"DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/date/cpu0:min:AVERAGE"

> >"LINE1:min#FF0000:cpu0"
> >
> >Error:
> >ERROR: formatstring wrong - mysql
>

> Second attempt at a solution, since the mysql drivers are confirmed to be
> present!
>
> See the documentation here
> http://oss.oetiker.ch/rrdtool/doc/rrdgraph_libdbi.en.html
>
> Your DEF specifies that the table to use is 'temperature', with the time
> coming from field 'date' and the value from field 'cpu0'.
>
> The 'date' field needs to be a UNIX timestamp, not a datetime field type.
> Prefix it with a '*' if it is a datetime field -- this requires RRDTool
> 1.4.something I believe.
>
> You've not given a table definition for your 'temperature' table, but I
> suspect 'date' is a datetime rather than a UNIX timestamp. Try this instead:
>
> DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0:min:AVERAGE
>
> ... and this should do the necessary datetime -> timestamp conversion.
>
> Steve
>
>
> Steve Shipway
> s.sh...@auckland.ac.nz
>
>

I use 'timestamp' (noted in my first post), not datetime.

Am I supposed to use UNIX_TIMESTAMP() some how?

mysqldump -u'thermal' -p'thermal' --no-data thermal

CREATE TABLE `temperature` (
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`cpuPhy` int(11) NOT NULL DEFAULT '0',
`cpu0` int(11) NOT NULL DEFAULT '0',
`cpu1` int(11) NOT NULL DEFAULT '0',
`cpu2` int(11) NOT NULL DEFAULT '0',
`cpu3` int(11) NOT NULL DEFAULT '0',
`disk0` int(11) NOT NULL DEFAULT '0',
`disk1` int(11) NOT NULL DEFAULT '0',
`disk2` int(11) NOT NULL DEFAULT '0',
`disk3` int(11) NOT NULL DEFAULT '0',
`board0` int(11) NOT NULL DEFAULT '0',
`board1` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[John Stile]

I did try both.
rrdtool graph ./test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600 DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0:min:AVERAGE
rrdtool graph ./test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600 DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/date/cpu0:min:AVERAGE
Both have the same error: ERROR: formatstring wrong - mysql
The error doesn't really tell me what is wrong.

Is is possible to modify the select called by rrdtool to force a UNIX_TIMESTAMP?

e.g. If I could have rrdtool performt this select, data will be correct.

SELECT UNIX_TIMESTAMP(date),cpu0 FROM temperature

Output looks like:
1394007172
41
1394007183
43
1394007243
41
1394007303
42

On Mon, 2014-03-31 at 21:19 +0000, Steve Shipway wrote:
> >rrdtool graph
> >./test.png --imgformat=PNG --start=-1day --end=+0hours --width=1000 --height=600
> >"DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/date/cpu0:min:AVERAGE"
> >"LINE1:min#FF0000:cpu0"
> >
> >Error:
> >ERROR: formatstring wrong - mysql
>
> Second attempt at a solution, since the mysql drivers are confirmed to be
> present!
>
> See the documentation here
> http://oss.oetiker.ch/rrdtool/doc/rrdgraph_libdbi.en.html
>
> Your DEF specifies that the table to use is 'temperature', with the time
> coming from field 'date' and the value from field 'cpu0'.
>
> The 'date' field needs to be a UNIX timestamp, not a datetime field type.
> Prefix it with a '*' if it is a datetime field -- this requires RRDTool
> 1.4.something I believe.
>
> You've not given a table definition for your 'temperature' table, but I
> suspect 'date' is a datetime rather than a UNIX timestamp. Try this instead:
>
> DEF:min=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0:min:AVERAGE
>
> ... and this should do the necessary datetime -> timestamp conversion.
>
> Steve
>
>
> Steve Shipway
> s.sh...@auckland.ac.nz
>
>

[John Stile]

Sorry for the trouble. I had a syntax error, as well as missing the *
in the date. Mysql TIMESTAMP dates required the * to force the unix
timestamp format.

This worked for me:
rrdtool graph /home/jstile/foo.png DEF:cpu0=sql//mysql/host=127.0.0.1/dbname=thermal/username=thermal/password=thermal//temperature/*date/cpu0/:avg:AVERAGE:step=600 AREA:user#66ff00:"cpu0"

thank you for your help.

[John Stile]

I found it nonintuitive, but the docs for libdbi,
REF: http://libdbi-drivers.sourceforge.net/docs/dbd_mysql.pdf
Under 4.1 MySQL (mis)features, "DATETIME, TIMESTAMP, DATE and TIME are
all treated as the DBI type DATETIME. This is currently a string, but
will change in later releases."