[dev.icinga.com #3466] New handling of NULL or zero timestamps breaks earlier versions of MySQL with current schema

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/3466

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 user.info] 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.)

Attachments

• 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

Changesets

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.

[icinga-migration]

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

[icinga-migration]

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

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

[icinga-migration]

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