mod_gearman in NagiosXi 5.7.1

[tgriep]

@YannPingot, the errors from earlier, suggests that the MYSQL tables did not get updated and the fix @swolf posted should of done that update.
Can you run the following as root and post the /tmp/info.txt file so we can see if the tables did get updated and a possible cause of the key too long error?

Code: Select all

echo 'SHOW FULL COLUMNS FROM nagios_hoststatus;' |mysql -t -u root -pnagiosxi nagios  >/tmp/info.txt
echo 'SHOW FULL COLUMNS FROM nagios_servicestatus;' |mysql -t -u root -pnagiosxi nagios >>/tmp/info.txt
echo 'SHOW FULL COLUMNS FROM nagios_objects;' |mysql -t -u root -pnagiosxi nagios >>/tmp/info.txt

Thanks

[YannPingot]

Here you go @tgriep, just FYI our database if offloaded :

Code: Select all

+-------------------------------+--------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
| Field                         | Type         | Collation       | Null | Key | Default             | Extra          | Privileges                      | Comment |
+-------------------------------+--------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
| hoststatus_id                 | int(11)      | NULL            | NO   | PRI | NULL                | auto_increment | select,insert,update,references |         |
| instance_id                   | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| host_object_id                | int(11)      | NULL            | NO   | UNI | 0                   |                | select,insert,update,references |         |
| status_update_time            | datetime     | NULL            | NO   | MUL | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| output                        | varchar(255) | utf8_general_ci | NO   |     |                     |                | select,insert,update,references |         |
| long_output                   | text         | utf8_general_ci | NO   |     | NULL                |                | select,insert,update,references |         |
| perfdata                      | text         | utf8_general_ci | NO   |     | NULL                |                | select,insert,update,references |         |
| current_state                 | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| has_been_checked              | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| should_be_scheduled           | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| current_check_attempt         | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| max_check_attempts            | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| last_check                    | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| next_check                    | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| check_type                    | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| check_options                 | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| last_state_change             | datetime     | NULL            | NO   | MUL | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_hard_state_change        | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_hard_state               | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| last_time_up                  | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_time_down                | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_time_unreachable         | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| state_type                    | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| last_notification             | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| next_notification             | datetime     | NULL            | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| no_more_notifications         | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| notifications_enabled         | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| problem_has_been_acknowledged | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| acknowledgement_type          | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| current_notification_number   | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| passive_checks_enabled        | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| active_checks_enabled         | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| event_handler_enabled         | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| flap_detection_enabled        | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| is_flapping                   | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| percent_state_change          | double       | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| latency                       | double       | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| execution_time                | double       | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| scheduled_downtime_depth      | smallint(6)  | NULL            | NO   | MUL | 0                   |                | select,insert,update,references |         |
| failure_prediction_enabled    | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| process_performance_data      | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| obsess_over_host              | smallint(6)  | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| modified_host_attributes      | int(11)      | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| event_handler                 | varchar(255) | utf8_general_ci | NO   |     |                     |                | select,insert,update,references |         |
| check_command                 | varchar(255) | utf8_general_ci | NO   |     |                     |                | select,insert,update,references |         |
| normal_check_interval         | double       | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| retry_check_interval          | double       | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
| check_timeperiod_object_id    | int(11)      | NULL            | NO   |     | 0                   |                | select,insert,update,references |         |
+-------------------------------+--------------+-----------------+------+-----+---------------------+----------------+---------------------------------+---------+
+-------------------------------+--------------+-------------------+------+-----+---------------------+----------------+---------------------------------+---------+
| Field                         | Type         | Collation         | Null | Key | Default             | Extra          | Privileges                      | Comment |
+-------------------------------+--------------+-------------------+------+-----+---------------------+----------------+---------------------------------+---------+
| servicestatus_id              | int(11)      | NULL              | NO   | PRI | NULL                | auto_increment | select,insert,update,references |         |
| instance_id                   | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| service_object_id             | int(11)      | NULL              | NO   | UNI | 0                   |                | select,insert,update,references |         |
| status_update_time            | datetime     | NULL              | NO   | MUL | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| output                        | varchar(255) | latin1_swedish_ci | NO   |     |                     |                | select,insert,update,references |         |
| long_output                   | text         | utf8_general_ci   | NO   |     | NULL                |                | select,insert,update,references |         |
| perfdata                      | text         | latin1_swedish_ci | NO   |     | NULL                |                | select,insert,update,references |         |
| current_state                 | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| has_been_checked              | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| should_be_scheduled           | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| current_check_attempt         | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| max_check_attempts            | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| last_check                    | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| next_check                    | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| check_type                    | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| check_options                 | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| last_state_change             | datetime     | NULL              | NO   | MUL | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_hard_state_change        | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_hard_state               | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| last_time_ok                  | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_time_warning             | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_time_unknown             | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| last_time_critical            | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| state_type                    | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| last_notification             | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| next_notification             | datetime     | NULL              | NO   |     | 1970-01-01 00:00:01 |                | select,insert,update,references |         |
| no_more_notifications         | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| notifications_enabled         | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| problem_has_been_acknowledged | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| acknowledgement_type          | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| current_notification_number   | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| passive_checks_enabled        | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| active_checks_enabled         | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| event_handler_enabled         | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| flap_detection_enabled        | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| is_flapping                   | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| percent_state_change          | double       | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| latency                       | double       | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| execution_time                | double       | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| scheduled_downtime_depth      | smallint(6)  | NULL              | NO   | MUL | 0                   |                | select,insert,update,references |         |
| failure_prediction_enabled    | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| process_performance_data      | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| obsess_over_service           | smallint(6)  | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| modified_service_attributes   | int(11)      | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| event_handler                 | varchar(255) | latin1_swedish_ci | NO   |     |                     |                | select,insert,update,references |         |
| check_command                 | varchar(255) | latin1_swedish_ci | NO   |     |                     |                | select,insert,update,references |         |
| normal_check_interval         | double       | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| retry_check_interval          | double       | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
| check_timeperiod_object_id    | int(11)      | NULL              | NO   |     | 0                   |                | select,insert,update,references |         |
+-------------------------------+--------------+-------------------+------+-----+---------------------+----------------+---------------------------------+---------+
+---------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field         | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+---------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| object_id     | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| instance_id   | smallint(6)  | NULL              | NO   |     | 0       |                | select,insert,update,references |         |
| objecttype_id | smallint(6)  | NULL              | NO   | MUL | 0       |                | select,insert,update,references |         |
| name1         | varchar(128) | latin1_swedish_ci | NO   | MUL |         |                | select,insert,update,references |         |
| name2         | varchar(128) | latin1_swedish_ci | YES  | MUL | NULL    |                | select,insert,update,references |         |
| is_active     | smallint(6)  | NULL              | NO   | MUL | 0       |                | select,insert,update,references |         |
+---------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

[tgriep]

Thanks for the data. Some of the tables did not get updated.
If you run the following commands on the Nagios server, that should update them. Replace xxx.xxx.xxx.xxx with the MYSQL server's IP address and adjust the username and password if needed.

Code: Select all

echo 'UPDATE `nagios_objects` SET `name1` = '' WHERE `name1` IS NULL;| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'UPDATE `nagios_objects` SET `name2` = '' WHERE `name2` IS NULL;| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` MODIFY `name1` varchar(1023) NOT NULL default "";'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` MODIFY `name2` varchar(1023) NOT NULL default "";| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `output` varchar(255) NOT NULL default "";| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `perfdata` TEXT NOT NULL;| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `event_handler` varchar(255) NOT NULL default "";| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `check_command` varchar(255) NOT NULL default "";| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` MODIFY `name1` varchar(1023) CHARACTER SET utf8 COLLATE utf8_unicode_ci;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` MODIFY `name2` varchar(1023) CHARACTER SET utf8 COLLATE utf8_unicode_ci;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `output` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `perfdata` TEXT  CHARACTER SET utf8 COLLATE utf8_unicode_ci;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `event_handler` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_servicestatus` MODIFY `check_command` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx

Restart nagios and see if the errors are getting logged into the nagios.log file.

[YannPingot]

Hello @tgriep,

As expected modifying 'name1' et 'name2' columns to the varchar(1023) didn't pass either, there are the same found in the update script.
The other ones passed, but errors in the log file are still the same.

According to the NDO logs in nagios.log, the 'long_output' column is also faulty (not only 'output'), in DB it's a TEXT with a utf8_general_ci collation, i've tried changing it to utf8_unicode_ci with no luck.

Regarding Gearman not handling checks, i wonder, is NDO3 supposed to handle them in case we're not using Gearman ? Or is it just a "passthrough" juste talking to the database as before ? In the source code of the NEB we can find functions about that :

Code: Select all

int ndo_neb_handle_service_check(int type, void * d)
{
    return ndo_handle_service_check(main_thread_context, type, d);
}

int ndo_handle_service_check(ndo_query_context * q_ctx, int type, void * d)
{
    if (ndo_failed_load) { return NDO_OK; }
    //ndo_log("handling service_check(int type, void * d)");
    trace_func_handler(service_check);

    nebstruct_service_check_data * data = d;
    int object_id = 0;

    int command_object_id = 0;

    /* this is the only data we care about / need */
    if (data->type != NEBTYPE_SERVICECHECK_PROCESSED) {
        trace_return_ok_cond("data->type != NEBTYPE_SERVICECHECK_PROCESSED");
    }

    object_id = ndo_get_object_id_name2(q_ctx, TRUE, NDO_OBJECTTYPE_SERVICE, data->host_name, data->service_description);

    if (data->command_name != NULL) {
        /* Nagios Core appears to always pass NULL for its command arguments when brokering NEBTYPE_SERVICECHECK_PROCESSED.
         * It's not clear why this was done, so we're working around it here for now.
         */
        command_object_id = ndo_get_object_id_name1(q_ctx, TRUE, NDO_OBJECTTYPE_COMMAND, data->command_name);
    }

Maybe the collation thing is not the real cause of Gearman not working as we would expect.

[tgriep]

@YannPingot
When you tried to update the name1 and name 2 fields in the nagios_objects table to varchar(1023), what sort of errors did you receive?
Can you run a repair of the database in case there is some sort of corruption that is causing the tables from getting updated?

Run the following as root on the nagios server and replace the xxx.xxx.xxx.xxx with the IP address of the MYSQL database server.

Code: Select all

mysqlcheck -f -r -u nagios -pnagios --database nagios -h xxx.xxx.xxx.xxx

Then see if you can update the tables.

You are correct, the collation changes we have been doing are to fix the missing changes to the database what NDO3 is generating when writing to the database and nor Mod Gearman.

The only way to fix the Mod Gearman issue for now is to go back to the ndomod broker module and the ndo2db daemon.

[YannPingot]

Hi @tgriep,

I have already done a mysqlcheck a while ago, still i've done another run and all tables are OK.

Unfortunately the problem still remains, and the error is :
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Regarding Gearman do you have an ETA for an NDO3 update compliant to it ? We're really waiting for it hoping it will improve our performances issues.
Our ipcs queue always start at something like 150k, and often goes up to 300-500k instead of going down to near 0, which render Nagios unusuable.
We've identified that ndo2db is the bottleneck here (or the broker module), our instance have around 24000 services.

[tgriep]

I am guessing that your server may be running a newer version on MYSQL and there are some global settings that need to be adjusted.
See this article for instructions on how to change the MYSQL settings if you need to.
https://support.nagios.com/kb/article/n ... s-780.html

After restarting MYSQL, try updating the nagios_objects table again by running the following in this order even if the above updates did not need to be done.

Code: Select all

echo 'ALTER TABLE `nagios_objects` drop KEY `name1`;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` drop KEY `name2`;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'UPDATE `nagios_objects` SET `name1` = '' WHERE `name1` IS NULL;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'UPDATE `nagios_objects` SET `name2` = '' WHERE `name2` IS NULL;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` MODIFY `name1` varchar(1023) NOT NULL default '';'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` MODIFY `name2` varchar(1023) NOT NULL default '';'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` DROP INDEX `objecttype_id`;'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx
echo 'ALTER TABLE `nagios_objects` ADD KEY `uniq_object` (`objecttype_id`,`name1`(162),`name2`(162));'| mysql -u nagios -pnagios --database nagios  -h xxx.xxx.xxx.xxx

The next version of XI is currently in testing and if all goes well, it should be released soon.
I can't give a definitive date but if testing goes well, maybe next week.