Uploaded image for project: 'Observium'
  1. Observium
  2. OBS-2756

Use of NOW() in SQL statement results in out-of-range SQL error, preventing sensor discovery

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • None
    • Community Edition
    • Discovery
    • Ubuntu 16.04 VM on ESXi 6.5
      MySQL 5.7.23

    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

          People

            landy Mike Stupalov
            KingJ K Jarrett
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: