Details
Major Description
I am running the latest community edition (17.9.0), but recently when adding a new host I noticed that CPU temperatures etc from lm_sensors were not being discovered by Observium. Running discovery.php in debug mode revealed that although they were being discovered, the sensor creation in the database was failing because the NOW() function in the SQL query returned a value that is out of the range of the sensor_polled column. NOW() returns a human readable value (e.g. '2018-08-26 18:21:15' or '20180826182107' in a numeric context), whereas the sensor_polled column is an int(11) column that appears to hold UNIX timestamps. Should the query switch to using the UNIX_TIMESTAMP() SQL function instead of NOW()?
SQL[INSERT INTO `sensors` (`poller_type`,`sensor_class`,`device_id`,`sensor_index`,`sensor_type`,`sensor_oid`,`sensor_descr`,`sensor_multiplier`,`sensor_deleted`,`sensor_limit`,`sensor_limit_warn`,`sensor_limit_low`,`sensor_limit_low_warn`,`entPhysicalIndex`,`entPhysicalClass`,`entPhysicalIndex_measured`,`measured_class`,`measured_entity`,`sensor_unit`,`sensor_value`,`sensor_polled`) VALUES ('snmp','temperature','48','5','lmsensors','.1.3.6.1.4.1.2021.13.16.2.1.3.5','Core 1','0.001','0','49.6',NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'31',NOW())]
|
SQL RUNTIME[0.00026822s]
|
ERROR[Error in query: (Out of range value for column 'sensor_polled' at row 1) 1264]
|
( inserted )
|
Attachments
Activity
I updated to the latest CE release (18.9.9411) and although I no longer get the error in this ticket, a new error related to default values prevents the sensor from being fully added;
SQL[INSERT INTO `sensors` (`poller_type`,`sensor_class`,`device_id`,`sensor_index`,`sensor_type`,`sensor_oid`,`sensor_descr`,`sensor_multiplier`,`sensor_deleted`,`sensor_limit`,`sensor_limit_warn`,`sensor_limit_low`,`sensor_limit_low_warn`,`entPhysicalIndex`,`entPhysicalClass`,`entPhysicalIndex_measured`,`measured_class`,`measured_entity`,`sensor_unit`,`sensor_value`,`sensor_polled`) VALUES ('snmp','temperature','48','1','lmsensors','.1.3.6.1.4.1.2021.13.16.2.1.3.1','temp1','0.001','0','44.48',NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'27.8','1535821085')]
|
SQL RUNTIME[0.00031614s]
|
ERROR[Error in query: (Field 'sensor_status' doesn't have a default value) 1364]
|
( inserted )
|
+
|
SQL[INSERT INTO `eventlog` (`device_id`,`entity_id`,`entity_type`,`timestamp`,`severity`,`message`) VALUES ('48',NULL,'sensor',NOW(),'6','Sensor added: temperature lmsensors 1 temp1')]
|
SQL RUNTIME[0.00091887s]
|
Discover sensor: temperature, kasumi.fc.kj, .1.3.6.1.4.1.2021.13.16.2.1.3.2, 2, lmsensors, temp2, SCALE: 0.001, LIMITS: (, , , ), CURRENT: 29.8, snmp, ,SQL[SELECT COUNT(`sensor_id`) FROM `sensors`
|
WHERE `poller_type`= 'snmp' AND `sensor_class` = 'temperature' AND `device_id` = '48' AND `sensor_type` = 'lmsensors' AND `sensor_index` = '2']
|
SQL RUNTIME[0.00017500s]SQL[INSERT INTO `sensors` (`poller_type`,`sensor_class`,`device_id`,`sensor_index`,`sensor_type`,`sensor_oid`,`sensor_descr`,`sensor_multiplier`,`sensor_deleted`,`sensor_limit`,`sensor_limit_warn`,`sensor_limit_low`,`sensor_limit_low_warn`,`entPhysicalIndex`,`entPhysicalClass`,`entPhysicalIndex_measured`,`measured_class`,`measured_entity`,`sensor_unit`,`sensor_value`,`sensor_polled`) VALUES ('snmp','temperature','48','2','lmsensors','.1.3.6.1.4.1.2021.13.16.2.1.3.2','temp2','0.001','0','47.68',NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'29.8','1535821085')]
|
SQL RUNTIME[0.00023794s]
|
ERROR[Error in query: (Field 'sensor_status' doesn't have a default value) 1364]
|
( inserted )
|
+
|
.No, too many changes in code since last CE release.
New CE will really soon 
Thanks Mike - is there a manual patch I can apply for now pending the next CE release?
Hrm, please attach debug for discovery:
./discovery.php -d -m sensors -h <device>
|
Your issues happened, because your server use STRICT_ALL_TABLES mode. We not use strict modes, and mostly all strict key disabled by default on poller/discovery start.
Missed this rare key, in new CE archive issue fixed now.
New version should be 18.9.9413