[Pkg-nagios-devel] Bug#292406: nagios-mysql: mysql error: Column 'last_log_rotation' cannot be null

[Brian May]

Package: nagios-mysql
Version: 2:1.3-0+pre6
Severity: important

I suddenly noticed lots of messages of the following in my
/var/log/messages file:

Jan 27 08:12:40 snoopy nagios: Error: Could not insert/update status
record for program in table 'programstatus' of database 'nagios'

I found the SQL query used in my mysql log files and re-executed
one by hand:

mysql> INSERT INTO programstatus (last_update,program_start,nagios_pid,daemon_mode,last_command_check,last_log_rotation,enable_notifications,execute_service_checks,accept_passive_service_checks,enable_event_handlers,obsess_over_services,enable_flap_detection,enable_failure_prediction,process_performance_data) VALUES (FROM_UNIXTIME(1106773720),FROM_UNIXTIME(1106772535),'3444','0',FROM_UNIXTIME(1106773719),FROM_UNIXTIME(0),'1','1','1','1','0','0','1','0')
-> ;
    ERROR 1048: Column 'last_log_rotation' cannot be null

This restriction comes from /usr/share/doc/nagios-mysql/create_mysql.gz:

CREATE TABLE programstatus (
   last_update datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   program_start datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   daemon_mode tinyint(4) DEFAULT '0' NOT NULL,
   nagios_pid int(11) DEFAULT '0' NOT NULL,
   last_command_check datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   last_log_rotation datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   execute_service_checks tinyint(4) DEFAULT '0' NOT NULL,
   accept_passive_service_checks tinyint(4) DEFAULT '0' NOT NULL,
   enable_event_handlers tinyint(4) DEFAULT '0' NOT NULL,
   obsess_over_services tinyint(4) DEFAULT '0' NOT NULL,
   enable_flap_detection tinyint(4) DEFAULT '0' NOT NULL,
   enable_notifications tinyint(4) DEFAULT '0' NOT NULL,
   enable_failure_prediction tinyint(4) DEFAULT '0' NOT NULL,
   process_performance_data tinyint(4) DEFAULT '0' NOT NULL
);

If I got this right (please check), the sql query sets last_log_rotation to
FROM_UNIXTIME(0). I am not sure if this was intended or not.

On one hand, I don't understand why this is getting interpreted as a NULL
value by mysql, on the other hand maybe this would be an ideal situation to
use a NULL value instead of insesrting an invalid time.

-- System Information:
Debian Release: 3.1
  APT prefers testing
  APT policy: (500, 'testing')
Architecture: i386 (i686)
Kernel: Linux 2.4.26
Locale: LANG=C, LC_CTYPE=C (charmap=ANSI_X3.4-1968)

Versions of packages nagios-mysql depends on:
ii  libc6                       2.3.2.ds1-20 GNU C Library: Shared libraries an
ii  libgd2-xpm                  2.0.33-1.1   GD Graphics Library version 2
ii  libjpeg62                   6b-9         The Independent JPEG Group's JPEG 
ii  libmysqlclient10            3.23.56-2    LGPL-licensed client library for M
ii  libpng12-0                  1.2.8rel-1   PNG library - runtime
ii  nagios-common               2:1.3-0+pre6 A host/service/network monitoring 
ii  zlib1g                      1:1.2.2-3    compression library - runtime

-- debconf information excluded