Skip to content
This repository has been archived by the owner. It is now read-only.

[ #3466] New handling of NULL or zero timestamps breaks earlier versions of MySQL with current schema #1174

icinga-migration opened this issue Nov 26, 2012 · 3 comments


None yet
1 participant
Copy link

commented Nov 26, 2012

This issue has been migrated from Redmine:

Created by crfriend on 2012-11-26 22:07:37 +00:00

Assignee: crfriend
Status: Resolved (closed on 2012-11-28 15:11:15 +00:00)
Target Version: 1.8.2
Last Update: 2014-12-08 14:35:05 +00:00 (in Redmine)

Icinga Version: 1.8.2
OS Version: Solaris 10, but others likely affected

The use of "FROM_UNIXTIME(NULL)" syntax in older versions of MySQL results in a NULL value being passed in inserts to tables where NULL values are disallowed for the affected column. E.g.:

Nov 26 16:35:25 t1 ido2db[12368]: [ID 827216] Error: database query failed for 'INSERT INTO icinga_programstatus (instance_id, status_update_time, program_start_time, is_currently_running, process_id, daemon_mode, last_command_check, last_log_rotation, notifications_enabled, active_service_checks_enabled, passive_service_checks_enabled, active_host_checks_enabled, passive_host_checks_enabled, event_handlers_enabled, flap_detection_enabled, failure_prediction_enabled, process_performance_data, obsess_over_hosts, obsess_over_services, modified_host_attributes, modified_service_attributes, global_host_event_handler, global_service_event_handler, disable_notif_expire_time) VALUES (1, FROM_UNIXTIME(1353965725), FROM_UNIXTIME(1353886511), '1', 12369, 1, FROM_UNIXTIME(1353965716), FROM_UNIXTIME(1353906000), 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, '', '', FROM_UNIXTIME(NULL))' - '1048: Column 'disable_notif_expire_time' cannot be null'

I suggest the use of "0000-00-00 00:00:00" which is explicitly legal and not NULL.

Patch attached. And, yes, I do agree with the assertion in the comment in the code.

My assertion of "High" priority is because the issue causes problems for anybody using older versions of MySQL with the default db schema for Icinga. (And, unless running very recent patches, memory leaks.)


  • MySQL-timestamp-patch.txt crfriend - 2012-11-26 22:07:37 +00:00 - Don't pass NULL timestamp, pass '0000-00-00 00:00:00' instead


2012-11-28 14:53:21 +00:00 by mfriedrich 1042107

idoutils: fix FROM_UNIXTIME(NULL) does not work with MySQL 5.0.x refs #3399 refs #3466

basically, we treated the inner 0L as NULL, as otherwise out-of-range
warnings would be expected. since this workaround does not work with
with mysql 5.0.x we must learn it the hard way, replacing 0L directly
with the null timestamp like NULL in mysql >= 5.1.x would automatically
create on schema insert - 0000-00-00 00:00:00

kudos to Carl for the patch and fix.

This comment has been minimized.

Copy link
Member Author

commented Nov 28, 2012

Updated by mfriedrich on 2012-11-28 14:57:15 +00:00

  • Status changed from New to Assigned
  • Assigned to set to crfriend
  • Target Version set to 1.8.2

this is possibly the reason for #3399

fixed version on 5.1.x looks like this.

Wed Nov 28 15:51:07 2012 .828515 [002.0] [pid=21166] [tid=140492717049600] UPDATE icinga_programstatus SET status_update_time=FROM_UNIXTIME(1354114265), program_start_time=FROM_UNIXTIME(1354113804), is_currently_running=1, process_id=9612, daemon_mode=1, last_command_check=FROM_UNIXTIME(1354114262), last_log_rotation='0000-00-00 00:00:00', notifications_enabled=1, active_service_checks_enabled=1, passive_service_checks_enabled=1, active_host_checks_enabled=1, passive_host_checks_enabled=1, event_handlers_enabled=1, flap_detection_enabled=1, failure_prediction_enabled=1, process_performance_data=1, obsess_over_hosts=0, obsess_over_services=0, modified_host_attributes=0, modified_service_attributes=0, global_host_event_handler='', global_service_event_handler='', disable_notif_expire_time='0000-00-00 00:00:00' WHERE instance_id=1

This comment has been minimized.

Copy link
Member Author

commented Nov 28, 2012

Updated by mfriedrich on 2012-11-28 15:11:15 +00:00

  • Status changed from Assigned to Resolved
  • Done % changed from 0 to 100

This comment has been minimized.

Copy link
Member Author

commented Dec 8, 2014

Updated by mfriedrich on 2014-12-08 14:35:05 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 79 to IDOUtils
  • OS Version set to Solaris 10, but others likely affected
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
You can’t perform that action at this time.