Mysql rrdtool gets NaN for mysql DB

A vanilla installation of librenms - no selinux
Functioning.
Output of validate.php

/opt/librenms# ./validate.php

Component Version
LibreNMS 1.46-40-g8bddfe722
DB Schema 273
PHP 7.2.10-0ubuntu0.18.04.1
MySQL 10.1.34-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.8.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
/opt/librenms#

ubuntu 18.04 LTS

unable to monitor its own mysql DB via snmpd
(mysql script installed and runs without error, returning data)

unable to monitor an external mysql DB via check_mk (unix-agent)
(mysql script installed for the agent and connected)

In both cases the DB is correctly discovered, both by snmpd and by the check_mk.socket agent.

In both cases the scripts that push the data are able to run without impediment.

rrdtool however, dumps NaN as the data.

NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN…

No data ever appears.

Installation of a server that has the applications postgres and an ntp-client gets data and reports and graphs it properly.

The two mysql DB instances are different - used for different purposes, and the scripts work locally and the DB installations are discovered. They just cannot report data.

Help?!

Run ./poller.php -d -h hostname -m applications and check the output for errors.

Good hint.

The relevant output here:

root@wlg-libremonitor-prod-01:~# /opt/librenms/poller.php -d -h localhost -m applications
LibreNMS Poller
SQL[select version from dbSchema order by version desc limit 1 0.37ms]

SQL[SELECT version() 0.16ms]

===================================
Version info:
Commit SHA: 8bddfe7225cb587cd1cab2fa5b9a5e4dc69f51e9
Commit Date: 1544275009
DB Schema: 273
PHP: 7.2.10-0ubuntu0.18.04.1
MySQL: 10.1.34-MariaDB-0ubuntu0.18.04.1
RRDTool: 1.7.0
SNMP: NET-SNMP 5.7.3
==================================DEBUG!
Updating os_def.cache…
Done
Override poller modules: applications
Starting polling run:

SQL[SELECT * FROM devices WHERE disabled = 0 AND hostname = ‘localhost’ ORDER BY device_id ASC 0.85ms]

SQL[SELECT * FROM devices_attribs WHERE device_id = ? [1] 0.2ms]

Hostname: localhost
Device ID: 1
OS: linux (unix)

[FPING] /usr/bin/fping -e -q -c 3 -p 500 -t 500 localhost
array (
‘xmt’ => ‘3’,
‘rcv’ => ‘3’,
‘loss’ => ‘0’,
‘min’ => ‘0.02’,
‘max’ => ‘0.03’,
‘avg’ => ‘0.02’,
‘exitcode’ => 0,
)
SQL[INSERT IGNORE INTO device_perf (xmt,rcv,loss,min,max,avg,device_id,timestamp,debug) VALUES (:xmt,:rcv,:loss,:min,:max,:avg,:device_id,NOW(),:debug) {“xmt”:“3”,“rcv”:“3”,“loss”:“0”,“min”:“0.02”,“max”:“0.03”,“avg”:“0.02”,“device_id”:1,“debug”:“”} 4.6ms]

SNMP Check response code: 0
Modules status: Global+
OS
Device

Load poller module core

SNMP[/usr/bin/snmpget -v2c -c COMMUNITY -OQnUt -m SNMPv2-MIB -M /opt/librenms/mibs:/opt/librenms/mibs/supermicro:/opt/librenms/mibs/dell udp:HOSTNAME:161 sysUpTime.0 sysLocation.0 sysContact.0 sysName.0 sysObjectID.0 sysDescr.0]
...0 = 4962225
...0 = Rack, Solnet Wellington RHEV
...0 = Your Name [email protected]
...0 = wlg-libremonitor-prod-01
...0 = ..4.1.8
...0 = Linux wlg-libremonitor-prod-01 4.15.0-38-generic #41-Ubuntu SMP Wed Oct 10 10:59:38 UTC 2018 x86_64
SNMP[/usr/bin/snmpget -v2c -c COMMUNITY -OQnUst -m HOST-RESOURCES-MIB:SNMP-FRAMEWORK-MIB -M /opt/librenms/mibs:/opt/librenms/mibs/supermicro:/opt/librenms/mibs/dell udp:HOSTNAME:161 snmpEngineTime.0 hrSystemUptime.0]
snmpEngineTime.0 = 49622
hrSystemUptime.0 = 118467188
Uptime seconds: 1184672
RRD[update /opt/librenms/rrd/localhost/uptime.rrd N:1184672]
Uptime: 13 days, 17h 4m 32s
SQL[select * from locations where (location = ?) limit 1 [“Rack, Solnet Wellington RHEV”] 0.29ms]

Runtime for poller module ‘core’: 0.0186 seconds with 503920 bytes
SNMP: [2/0.01s] MySQL: [0/0.00s] RRD: [1/0.00s]

Unload poller module core

RRD[update /opt/librenms/rrd/localhost/poller-perf-core.rrd N:0.018554210662842]
Modules status: Global+
OS
Device

Load poller module applications

SQL[SELECT * FROM applications WHERE device_id = ? [1] 0.44ms]

SNMP[/usr/bin/snmpget -v2c -c COMMUNITY -Ovq -M /opt/librenms/mibs:/opt/librenms/mibs/supermicro:/opt/librenms/mibs/dell udp:HOSTNAME:161 .1.3.6.1.4.1.8072.1.3.2.3.1.2.5.109.121.115.113.108]
“MySQL: Access denied for user ‘root’@‘localhost’”
mysqlRRD[update /opt/librenms/rrd/localhost/app-mysql-1.rrd N:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U]
RRD[update /opt/librenms/rrd/localhost/app-mysql-1-status.rrd N:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U:U]
SQL[UPDATE applications set app_state=?,app_status=?,timestamp=NOW() WHERE app_id = ? [“OK”,“”,1] 3.39ms]

SQL[SELECT * FROM application_metrics WHERE app_id=? [1] 0.47ms]

: …
ntp-clientSNMP[/usr/bin/snmpget -v2c -c COMMUNITY -Oqv -m NET-SNMP-EXTEND-MIB -M /opt/librenms/mibs:/opt/librenms/mibs/supermicro:/opt/librenms/mibs/dell udp:HOSTNAME:161 nsExtendOutputFull.10.110.116.112.45.99.108.105.101.110.116]
{"data":{"offset":"0.218504","frequency":"7.194","sys_jitter":"0.024015","clk_jitter":"0.356","clk_wander":"0.068"},"version":"4","error":"0","errorString":""}
RRD[update /opt/librenms/rrd/localhost/app-ntp-client-6.rrd N:0.218504:7.194:0.024015:0.356:0.068]
SQL[UPDATE applications set app_state=?,app_status=?,timestamp=NOW() WHERE app_id = ? [“OK”,“”,6] 2.95ms]

SQL[SELECT * FROM application_metrics WHERE app_id=? [6] 0.37ms]

: …

Now I can log into mariadb as root with the password used.

However, in the run just before this, the snmpd could not open the mysql.cnf file owned by root either.

Running the /etc/snmp/mysql script as root - worked.

Similar problem was noted in the postgres implementation earlier - the .cnf was not working for them so they had the credentials directly in their script. So I went into the /etc/snmp/mysql script, took out the mysql.cnf dependency and put the credentials directly in the mysql script so that the snmp and script could work. Still no joy, so a simpler test.

/etc/snmp# su - -s /bin/bash librenms
$ cd /etc/snmp
$ ./mysql
MySQL: Access denied for user ‘root’@‘localhost’$

root@local:/etc/snmp# ls -l
total 72
-rwxr-xr-x 1 root root 51987 Dec 11 07:22 mysql
-rw-rw-rw- 1 root root 109 Dec 11 07:17 mysql.cnf.broke
-rwxr-xr-x 1 root root 1733 Dec 10 17:47 ntp-client
-rw-r–r-- 1 root root 211 Feb 21 2018 snmp.conf
-rw------- 1 root root 848 Dec 10 17:48 snmpd.conf

This is apparently something peculiar in the mysql config relating to how it treats root and localhost and I will post again when I work it out.

Turns out that the default installation of MariaDB sets the root user as identified by SOCKET and so
the ‘root’@‘password’ formulation in the script and the cnf file cannot work.

A separate ADMIN user needs to be created per

" ```
CREATE USER admin@localhost IDENTIFIED BY ‘password’;
GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;

((note that the asterisks needed an escape to be displayed, which you’d not want in the actual cmd))
This allows librenms to access the DB using ‘admin’@‘localhost’ in place of root.

Using the new “admin” user works.

Data is coming through now.