Nagios Support Forum

Posted: **Thu Apr 22, 2021 9:21 am**

Can you help me with this please.

https://imgur.com/a/BDc79Hg

Database maintenance in error.

Posted: **Thu Apr 22, 2021 6:08 pm**

Try deleting this file if it exists:

rm -rf /usr/local/nagiosxi/var/dbmaint.lock

If that doesn't resolve it, please PM me a copy of your profile, you can download it from Admin > System Profile by clicking the Download Profile button.

Thank you!

Posted: **Mon Apr 26, 2021 8:50 am**

I just send you the profile. I deleted the lock file and the problem stay.
Thx in advance for helping.

Posted: **Mon Apr 26, 2021 9:24 am**

I see this error message

Code: Select all

SQL: OPTIMIZE TABLE nagios_timeperiods
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1619417701) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1619417701) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1619360101)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1611670501)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Warning:  Invalid argument supplied for foreach() in /usr/local/nagiosxi/cron/dbmaint.php on line 203
SQL2: Deleted 0 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1616854501)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Fatal error:  Call to a member function GetArray() on a non-object in /usr/local/nagiosxi/cron/dbmaint.php on line 221

Posted: **Mon Apr 26, 2021 10:25 am**

Please edit your /etc/my.cnf and add these under the [mysqld] section:

Code: Select all

max_allowed_packet=512M
max_connections=800

Then restart mariadb/httpd:

Code: Select all

systemctl restart mariadb httpd

See if that alleviates the "MySQL server has gone away" messages.

Additionally, please send the output of this command:
- NOTE: You may need to adjust the -h 127.0.0.1, the -uroot, and -pnagiosxi in the command if your DB is offloaded to another server and/or you've changed the root mysql password

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

Unrelated, but you should set your load_threshold to 60.0 and your TIMEOUT to 20 following this KB article, that will help prevent gaps in your graphs:

https://support.nagios.com/kb/article.php?id=9

Posted: **Thu Apr 29, 2021 8:33 am**

Code: Select all

+--------------------------------------------+------------+
| Table                                      | Size in MB |
+--------------------------------------------+------------+
| nagios_acknowledgements                    |       2.92 |
| nagios_commands                            |       0.06 |
| nagios_commenthistory                      |    4817.00 |
| nagios_comments                            |       2.09 |
| nagios_configfiles                         |       0.03 |
| nagios_configfilevariables                 |       0.02 |
| nagios_conninfo                            |       2.52 |
| nagios_contact_addresses                   |       0.03 |
| nagios_contact_notificationcommands        |       0.11 |
| nagios_contactgroup_members                |       0.03 |
| nagios_contactgroups                       |       0.03 |
| nagios_contactnotificationmethods          |       6.55 |
| nagios_contactnotifications                |       9.06 |
| nagios_contacts                            |       0.03 |
| nagios_contactstatus                       |       0.03 |
| nagios_customvariables                     |       6.27 |
| nagios_customvariablestatus                |       5.28 |
| nagios_dbversion                           |       0.02 |
| nagios_downtimehistory                     |     234.34 |
| nagios_eventhandlers                       |       0.03 |
| nagios_externalcommands                    |       3.52 |
| nagios_flappinghistory                     |      13.52 |
| nagios_host_contactgroups                  |       2.41 |
| nagios_host_contacts                       |       0.30 |
| nagios_host_parenthosts                    |       0.17 |
| nagios_hostchecks                          |       0.03 |
| nagios_hostdependencies                    |       0.03 |
| nagios_hostescalation_contactgroups        |       0.03 |
| nagios_hostescalation_contacts             |       0.03 |
| nagios_hostescalations                     |       0.03 |
| nagios_hostgroup_members                   |       1.53 |
| nagios_hostgroups                          |       0.08 |
| nagios_hosts                               |       3.27 |
| nagios_hoststatus                          |       4.73 |
| nagios_instances                           |       0.02 |
| nagios_logentries                          |    1800.48 |
| nagios_notifications                       |       6.92 |
| nagios_objects                             |      11.44 |
| nagios_processevents                       |       1.52 |
| nagios_programstatus                       |       0.03 |
| nagios_runtimevariables                    |       0.03 |
| nagios_scheduleddowntime                   |       1.30 |
| nagios_service_contactgroups               |       3.03 |
| nagios_service_contacts                    |       2.22 |
| nagios_service_parentservices              |       0.03 |
| nagios_servicechecks                       |       0.06 |
| nagios_servicedependencies                 |       0.03 |
| nagios_serviceescalation_contactgroups     |       0.03 |
| nagios_serviceescalation_contacts          |       0.03 |
| nagios_serviceescalations                  |       0.03 |
| nagios_servicegroup_members                |       0.27 |
| nagios_servicegroups                       |       0.03 |
| nagios_services                            |       8.53 |
| nagios_servicestatus                       |      20.72 |
| nagios_statehistory                        |    1578.41 |
| nagios_systemcommands                      |       0.09 |
| nagios_timedeventqueue                     |       0.09 |
| nagios_timedevents                         |       0.09 |
| nagios_timeperiod_timeranges               |       0.03 |
| nagios_timeperiods                         |       0.03 |
| tbl_command                                |       0.08 |
| tbl_contact                                |       0.03 |
| tbl_contactgroup                           |       0.03 |
| tbl_contacttemplate                        |       0.03 |
| tbl_domain                                 |       0.03 |
| tbl_host                                   |       1.73 |
| tbl_hostdependency                         |       0.03 |
| tbl_hostescalation                         |       0.03 |
| tbl_hostextinfo                            |       0.03 |
| tbl_hostgroup                              |       0.11 |
| tbl_hosttemplate                           |       0.03 |
| tbl_info                                   |       0.17 |
| tbl_lnkContactToCommandHost                |       0.02 |
| tbl_lnkContactToCommandService             |       0.02 |
| tbl_lnkContactToContactgroup               |       0.02 |
| tbl_lnkContactToContacttemplate            |       0.02 |
| tbl_lnkContactToVariabledefinition         |       0.02 |
| tbl_lnkContactgroupToContact               |       0.02 |
| tbl_lnkContactgroupToContactgroup          |       0.02 |
| tbl_lnkContacttemplateToCommandHost        |       0.02 |
| tbl_lnkContacttemplateToCommandService     |       0.02 |
| tbl_lnkContacttemplateToContactgroup       |       0.02 |
| tbl_lnkContacttemplateToContacttemplate    |       0.02 |
| tbl_lnkContacttemplateToVariabledefinition |       0.02 |
| tbl_lnkHostToContact                       |       0.02 |
| tbl_lnkHostToContactgroup                  |       0.02 |
| tbl_lnkHostToHost                          |       0.14 |
| tbl_lnkHostToHostgroup                     |       0.19 |
| tbl_lnkHostToHosttemplate                  |       0.36 |
| tbl_lnkHostToVariabledefinition            |       0.02 |
| tbl_lnkHostdependencyToHost_DH             |       0.02 |
| tbl_lnkHostdependencyToHost_H              |       0.02 |
| tbl_lnkHostdependencyToHostgroup_DH        |       0.02 |
| tbl_lnkHostdependencyToHostgroup_H         |       0.02 |
| tbl_lnkHostescalationToContact             |       0.02 |
| tbl_lnkHostescalationToContactgroup        |       0.02 |
| tbl_lnkHostescalationToHost                |       0.02 |
| tbl_lnkHostescalationToHostgroup           |       0.02 |
| tbl_lnkHostgroupToHost                     |       0.13 |
| tbl_lnkHostgroupToHostgroup                |       0.02 |
| tbl_lnkHosttemplateToContact               |       0.02 |
| tbl_lnkHosttemplateToContactgroup          |       0.02 |
| tbl_lnkHosttemplateToHost                  |       0.02 |
| tbl_lnkHosttemplateToHostgroup             |       0.02 |
| tbl_lnkHosttemplateToHosttemplate          |       0.02 |
| tbl_lnkHosttemplateToVariabledefinition    |       0.02 |
| tbl_lnkServiceToContact                    |       0.02 |
| tbl_lnkServiceToContactgroup               |       0.05 |
| tbl_lnkServiceToHost                       |       1.52 |
| tbl_lnkServiceToHostgroup                  |       0.02 |
| tbl_lnkServiceToServicegroup               |       0.02 |
| tbl_lnkServiceToServicetemplate            |       1.47 |
| tbl_lnkServiceToVariabledefinition         |       0.02 |
| tbl_lnkServicedependencyToHost_DH          |       0.02 |
| tbl_lnkServicedependencyToHost_H           |       0.02 |
| tbl_lnkServicedependencyToHostgroup_DH     |       0.02 |
| tbl_lnkServicedependencyToHostgroup_H      |       0.02 |
| tbl_lnkServicedependencyToService_DS       |       0.02 |
| tbl_lnkServicedependencyToService_S        |       0.02 |
| tbl_lnkServicedependencyToServicegroup_DS  |       0.02 |
| tbl_lnkServicedependencyToServicegroup_S   |       0.02 |
| tbl_lnkServiceescalationToContact          |       0.02 |
| tbl_lnkServiceescalationToContactgroup     |       0.02 |
| tbl_lnkServiceescalationToHost             |       0.02 |
| tbl_lnkServiceescalationToHostgroup        |       0.02 |
| tbl_lnkServiceescalationToService          |       0.02 |
| tbl_lnkServiceescalationToServicegroup     |       0.02 |
| tbl_lnkServicegroupToService               |       0.02 |
| tbl_lnkServicegroupToServicegroup          |       0.02 |
| tbl_lnkServicetemplateToContact            |       0.02 |
| tbl_lnkServicetemplateToContactgroup       |       0.02 |
| tbl_lnkServicetemplateToHost               |       0.02 |
| tbl_lnkServicetemplateToHostgroup          |       0.02 |
| tbl_lnkServicetemplateToServicegroup       |       0.02 |
| tbl_lnkServicetemplateToServicetemplate    |       0.02 |
| tbl_lnkServicetemplateToVariabledefinition |       0.02 |
| tbl_lnkTimeperiodToTimeperiod              |       0.02 |
| tbl_logbook                                |       0.08 |
| tbl_mainmenu                               |       0.02 |
| tbl_permission                             |       0.02 |
| tbl_permission_inactive                    |       0.02 |
| tbl_service                                |       3.52 |
| tbl_servicedependency                      |       0.03 |
| tbl_serviceescalation                      |       0.03 |
| tbl_serviceextinfo                         |       0.03 |
| tbl_servicegroup                           |       0.03 |
| tbl_servicetemplate                        |       0.13 |
| tbl_session                                |       0.02 |
| tbl_session_locks                          |       0.02 |
| tbl_settings                               |       0.03 |
| tbl_submenu                                |       0.02 |
| tbl_timedefinition                         |       0.02 |
| tbl_timeperiod                             |       0.03 |
| tbl_user                                   |       0.03 |
| tbl_variabledefinition                     |       0.06 |
| xi_auditlog                                |       4.80 |
| xi_auth_tokens                             |       1.56 |
| xi_cmp_trapdata                            |       0.03 |
| xi_cmp_trapdata_log                        |       0.03 |
| xi_commands                                |       0.34 |
| xi_eventqueue                              |       0.03 |
| xi_events                                  |    1317.38 |
| xi_incidents                               |       0.02 |
| xi_meta                                    |   24245.98 |
| xi_mibs                                    |       0.05 |
| xi_options                                 |       0.08 |
| xi_sessions                                |       3.61 |
| xi_sysstat                                 |       0.03 |
| xi_usermeta                                |       4.83 |
| xi_users                                   |       0.06 |
+--------------------------------------------+------------+

Posted: **Thu Apr 29, 2021 8:40 am**

I apply all the change you recommend and restart mariadb and httpd

Posted: **Thu Apr 29, 2021 1:01 pm**

The problem persist. Any other advice ?

Thx in advance!

Posted: **Thu Apr 29, 2021 6:12 pm**

You're hitting a bug (those are temporary tables and should be cleaned out automatically):

Code: Select all

| xi_events                                  |    1317.38 |
| xi_meta                                    |   24245.98 |

Please run this command to fix it:

Code: Select all

echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosxi

Then go to Admin > Performance Settings > Databases tab and adjust ALL THREE Optimize Intervals to 300.

See if that resolves it (it should).

Posted: **Fri Apr 30, 2021 8:08 am**

I truncate the tables and change the parameters. I problem persist.

Code: Select all

SQL: OPTIMIZE TABLE nagios_timeperiods
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1619757902) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1619757902) AND status_code = 2
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'auth_tokens'...
SQL: DELETE FROM xi_auth_tokens WHERE auth_valid_until < FROM_UNIXTIME(1619700302)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
CLEANING nagiosxi TABLE 'cmp_trapdata_log'...
SQL: DELETE FROM xi_cmp_trapdata_log WHERE trapdata_log_datetime < FROM_UNIXTIME(1612010702)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Warning:  Invalid argument supplied for foreach() in /usr/local/nagiosxi/cron/dbmaint.php on line 203
SQL2: Deleted 0 (DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL))
CLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1617194702)
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
    <p><pre>SQL Error [nagiosxi] : MySQL server has gone away</pre></p>
PHP Fatal error:  Call to a member function GetArray() on a non-object in /usr/local/nagiosxi/cron/dbmaint.php on line 221

I try the same command directly in mysql interactive command.

Code: Select all

MariaDB [nagiosxi]> DELETE from xi_commands WHERE processing_time < FROM_UNIXTIME(1619757902) AND status_code = 2;
Query OK, 430 rows affected (0.00 sec)

We converted all our DB to innodb couple week ago. Do you think the convertion maybe brake couple table. Maybe we can compare those table with one of your good lab instance.

Code: Select all

MariaDB [nagiosxi]> desc xi_commands;
+--------------------+-----------+------+-----+---------------------+-----------------------------+
| Field              | Type      | Null | Key | Default             | Extra                       |
+--------------------+-----------+------+-----+---------------------+-----------------------------+
| command_id         | int(11)   | NO   | PRI | NULL                | auto_increment              |
| group_id           | int(11)   | YES  |     | 0                   |                             |
| submitter_id       | int(11)   | YES  |     | 0                   |                             |
| beneficiary_id     | int(11)   | YES  |     | 0                   |                             |
| command            | int(11)   | NO   |     | NULL                |                             |
| submission_time    | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| event_time         | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| frequency_type     | int(11)   | YES  |     | 0                   |                             |
| frequency_units    | int(11)   | YES  |     | 0                   |                             |
| frequency_interval | int(11)   | YES  |     | 0                   |                             |
| processing_time    | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| status_code        | int(11)   | YES  |     | 0                   |                             |
| result_code        | int(11)   | YES  |     | 0                   |                             |
| command_data       | text      | YES  |     | NULL                |                             |
| result             | text      | YES  |     | NULL                |                             |
+--------------------+-----------+------+-----+---------------------+-----------------------------+
15 rows in set (0.01 sec)
MariaDB [nagiosxi]> desc xi_auth_tokens;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| auth_token_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| auth_user_id      | int(11)      | NO   |     | NULL    |                |
| auth_session_id   | int(11)      | NO   |     | NULL    |                |
| auth_token        | varchar(128) | YES  |     | NULL    |                |
| auth_valid_until  | datetime     | YES  |     | NULL    |                |
| auth_expires_at   | datetime     | YES  |     | NULL    |                |
| auth_restrictions | mediumtext   | YES  |     | NULL    |                |
| auth_used         | smallint(6)  | YES  |     | 0       |                |
+-------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
MariaDB [nagiosxi]> desc xi_events;
+-----------------+-------------+------+-----+---------------------+-----------------------------+
| Field           | Type        | Null | Key | Default             | Extra                       |
+-----------------+-------------+------+-----+---------------------+-----------------------------+
| event_id        | int(11)     | NO   | PRI | NULL                | auto_increment              |
| event_time      | timestamp   | NO   | MUL | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| event_source    | smallint(6) | YES  | MUL | NULL                |                             |
| event_type      | smallint(6) | NO   |     | 0                   |                             |
| status_code     | smallint(6) | NO   |     | 0                   |                             |
| processing_time | timestamp   | NO   |     | 0000-00-00 00:00:00 |                             |
+-----------------+-------------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)
MariaDB [nagiosxi]> desc xi_cmp_trapdata_log;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field                       | Type         | Null | Key | Default | Extra          |
+-----------------------------+--------------+------+-----+---------+----------------+
| trapdata_log_id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| trapdata_log_event_name     | varchar(128) | NO   |     | NULL    |                |
| trapdata_log_event_oid      | varchar(50)  | NO   |     | NULL    |                |
| trapdata_log_numeric_oid    | varchar(100) | YES  |     | NULL    |                |
| trapdata_log_symbolic_oid   | varchar(100) | YES  |     | NULL    |                |
| trapdata_log_community      | varchar(20)  | YES  |     | NULL    |                |
| trapdata_log_trap_hostname  | varchar(100) | YES  |     | NULL    |                |
| trapdata_log_trap_ip        | varchar(16)  | YES  |     | NULL    |                |
| trapdata_log_agent_hostname | varchar(100) | YES  |     | NULL    |                |
| trapdata_log_agent_IP       | varchar(16)  | YES  |     | NULL    |                |
| trapdata_log_category       | varchar(20)  | NO   |     | NULL    |                |
| trapdata_log_severity       | varchar(20)  | NO   |     | NULL    |                |
| trapdata_log_uptime         | varchar(20)  | NO   |     | NULL    |                |
| trapdata_log_datetime       | datetime     | YES  |     | NULL    |                |
| trapdata_log_bindings       | text         | YES  |     | NULL    |                |
+-----------------------------+--------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)
MariaDB [nagiosxi]> desc xi_meta;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| meta_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| metatype_id | int(11)      | YES  |     | 0       |                |
| metaobj_id  | int(11)      | YES  |     | 0       |                |
| keyname     | varchar(128) | NO   |     | NULL    |                |
| keyvalue    | text         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [nagiosxi]> desc xi_auditlog;
+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field       | Type         | Null | Key | Default           | Extra                       |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| auditlog_id | int(11)      | NO   | PRI | NULL              | auto_increment              |
| log_time    | timestamp    | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| source      | text         | YES  |     | NULL              |                             |
| user        | varchar(200) | YES  | MUL | NULL              |                             |
| type        | int(11)      | YES  | MUL | NULL              |                             |
| message     | text         | YES  |     | NULL              |                             |
| ip_address  | varchar(45)  | YES  | MUL | NULL              |                             |
| details     | text         | YES  |     | NULL              |                             |
+-------------+--------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

Nagios Support Forum

Database maintenance in error

Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error

Re: Database maintenance in error