[rrd-users] rrd dbi syntx

[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."

On Wed, 2014-04-02 at 22:54 -0700, John Stile wrote:
> 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.
> 
> On Wed, 2014-04-02 at 21:03 -0700, John Stile wrote:
> > 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.shipway at auckland.ac.nz
> > > 
> > > 
> > 
> > 
> > _______________________________________________
> > rrd-users mailing list
> > rrd-users at lists.oetiker.ch
> > https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users
> 
> 
> _______________________________________________
> rrd-users mailing list
> rrd-users at lists.oetiker.ch
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users