Nagios fusion failed to install on RHEL 9 with DB related error.

[SBhambri]

o Nagios fusion failed to install on RHEL 9.
o I tried to run DB command to create table manually, below and got same error "ERROR 1067 (42000): Invalid default value for 'scheduled_time'"


[root@xxxx nagiosfusion]# mysql -pfusion
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE TABLE IF NOT EXISTS `fusion`.`commands` (
-> `command_id` int unsigned auto_increment,
-> `user_id` int unsigned not null default 0,
-> `command` smallint unsigned not null default 0,
-> `submission_time` timestamp not null default current_timestamp,
-> `scheduled_time` timestamp not null default '1970-01-01 01:00:00',
-> `start_time` timestamp not null default '1970-01-01 01:00:00',
-> `processing_time` int,
-> `status_code` int not null default 0,
-> `result_code` int not null default 0,
-> `command_data` text,
-> `result` text,
-> primary key (`command_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ERROR 1067 (42000): Invalid default value for 'scheduled_time'
MariaDB [(none)]>

[SBhambri]

Hi Team,

I looked at my old fusion server (On RHEL 7) that I am replacing and found below DB Table settings:

##############
MariaDB [fusion]> SHOW COLUMNS FROM commands
-> ;
+-----------------+----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------------------+------+-----+---------------------+----------------+
| command_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | | 0 | |
| command | smallint(5) unsigned | NO | | 0 | |
| submission_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| scheduled_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| start_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| processing_time | int(11) | YES | | NULL | |
| status_code | int(11) | NO | | 0 | |
| result_code | int(11) | NO | | 0 | |
| command_data | text | YES | | NULL | |
| result | text | YES | | NULL | |
+-----------------+----------------------+------+-----+---------------------+----------------+
##############

I modified the current installation script to execute the modified "db/schemas/fusion.sql"

FROM:
##############################################################################
CREATE TABLE IF NOT EXISTS `fusion`.`commands` (
`command_id` int unsigned auto_increment,

`user_id` int unsigned not null default 0,

`command` smallint unsigned not null default 0,

`submission_time` timestamp not null default current_timestamp,
`scheduled_time` timestamp not null default '1970-01-01 01:00:00',
`start_time` timestamp not null default '1970-01-01 01:00:00',
`processing_time` int,

`status_code` int not null default 0,
`result_code` int not null default 0,

`command_data` text,
`result` text,

primary key (`command_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


[root@geqmfu01 nagiosfusion]# cat setup/db/schemas/fusion.sql.org2

CREATE DATABASE IF NOT EXISTS `fusion`;
GRANT ALL ON fusion.* TO 'fusion'@'localhost';


##############################################################################

TO:

##############################################################################
CREATE TABLE IF NOT EXISTS `fusion`.`commands` (
`command_id` int unsigned auto_increment,

`user_id` int unsigned not null default 0,

`command` smallint unsigned not null default 0,

`submission_time` timestamp not null default current_timestamp,
`scheduled_time` timestamp not null default '0000-00-00 00:00:00',
`start_time` timestamp not null default '0000-00-00 00:00:00',
`processing_time` int,

`status_code` int not null default 0,
`result_code` int not null default 0,

`command_data` text,
`result` text,

primary key (`command_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


[root@geqmfu01 nagiosfusion]# cat setup/db/schemas/fusion.sql.org2

CREATE DATABASE IF NOT EXISTS `fusion`;
GRANT ALL ON fusion.* TO 'fusion'@'localhost';


##############################################################################

Please confirm if this approach is okay.

With Regards,

SB

[swolf]

Hi @SBhambri,

Thanks for reaching out. If you're able to get to the end of the install process, your approach looks reasonable to me for your system. I've filed a bug report for this issue.

-Sebastian