Can you help me with this please.
https://imgur.com/a/BDc79Hg
Database maintenance in error.
Database maintenance in error
-
ssax
- Dreams In Code
- Posts: 7682
- Joined: Wed Feb 11, 2015 12:54 pm
Re: Database maintenance in error
Try deleting this file if it exists:
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!
Code: Select all
rm -rf /usr/local/nagiosxi/var/dbmaint.lockThank you!
-
bennyboy
- Posts: 157
- Joined: Thu Oct 29, 2015 9:42 am
Re: Database maintenance in error
I just send you the profile. I deleted the lock file and the problem stay.
Thx in advance for helping.
Thx in advance for helping.
-
bennyboy
- Posts: 157
- Joined: Thu Oct 29, 2015 9:42 am
Re: Database maintenance in error
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
-
ssax
- Dreams In Code
- Posts: 7682
- Joined: Wed Feb 11, 2015 12:54 pm
Re: Database maintenance in error
Please edit your /etc/my.cnf and add these under the [mysqld] section:
Then 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
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
Code: Select all
max_allowed_packet=512M
max_connections=800Code: Select all
systemctl restart mariadb httpdAdditionally, 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 --tablehttps://support.nagios.com/kb/article.php?id=9
-
bennyboy
- Posts: 157
- Joined: Thu Oct 29, 2015 9:42 am
Re: Database maintenance in error
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 |
+--------------------------------------------+------------+
-
bennyboy
- Posts: 157
- Joined: Thu Oct 29, 2015 9:42 am
Re: Database maintenance in error
I apply all the change you recommend and restart mariadb and httpd
-
bennyboy
- Posts: 157
- Joined: Thu Oct 29, 2015 9:42 am
Re: Database maintenance in error
The problem persist. Any other advice ?
Thx in advance!
Thx in advance!
-
ssax
- Dreams In Code
- Posts: 7682
- Joined: Wed Feb 11, 2015 12:54 pm
Re: Database maintenance in error
You're hitting a bug (those are temporary tables and should be cleaned out automatically):
Please run this command to fix it:
Then go to Admin > Performance Settings > Databases tab and adjust ALL THREE Optimize Intervals to 300.
See if that resolves it (it should).
Code: Select all
| xi_events | 1317.38 |
| xi_meta | 24245.98 |Code: Select all
echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosxiSee if that resolves it (it should).
-
bennyboy
- Posts: 157
- Joined: Thu Oct 29, 2015 9:42 am
Re: Database maintenance in error
I truncate the tables and change the parameters. I problem persist.
I try the same command directly in mysql interactive command.
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
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
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)