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

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

          [OBS-2756] Use of NOW() in SQL statement results in out-of-range SQL error, preventing sensor discovery
          KingJ K Jarrett added a comment -

          Understandable, thanks. I'll keep an eye out for the new CE release  .

          KingJ K Jarrett added a comment - Understandable, thanks. I'll keep an eye out for the new CE release  .
          landy Mike Stupalov added a comment - - edited

          No, too many changes in code since last CE release.
          New CE will really soon

          landy Mike Stupalov added a comment - - edited No, too many changes in code since last CE release. New CE will really soon
          KingJ K Jarrett added a comment -

          Thanks Mike - is there a manual patch I can apply for now pending the next CE release?

          KingJ K Jarrett added a comment - Thanks Mike - is there a manual patch I can apply for now pending the next CE release?

          Ok, tnx.
          Fixed in r9400. (Will be in next CE release soon).

          landy Mike Stupalov added a comment - Ok, tnx. Fixed in r9400. (Will be in next CE release soon).
          KingJ K Jarrett added a comment -

          Attached.

          KingJ K Jarrett added a comment - Attached.

          Hrm, please attach debug for discovery:

          ./discovery.php -d -m sensors -h <device>
          

          landy Mike Stupalov added a comment - Hrm, please attach debug for discovery: ./discovery.php -d -m sensors -h <device>

          People

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

            Dates

              Created:
              Updated:
              Resolved: