DB ERROR

[ITOMB_IMT]

Hi,

we have offloaded database to a remote server log time ago using this doc, https://assets.nagios.com/downloads/nag ... Server.pdf. All of a sudden after reboot, the mysql is running into errors. I checked all the services on both App and DB servers, all are working fine.

when i run a command to login to DB from App server, check the status of DB's i got the following errors.

Code: Select all

mysql -u root -p'root' -h DB_server -e STATUS;
ERROR 2003 (HY000): Can't connect to MySQL server on 'DB_server' (113)

mysql -u nagios -p'nagios' -h DB_server -e STATUS;
ERROR 2003 (HY000): Can't connect to MySQL server on 'DB_server' (113)

when i check mariadb.log, i see these errors

Code: Select all

190408 13:24:51 [ERROR] Cannot find or open table nagiosxi/xi_sysstat from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

There are many such type of nagiosxi DB errors.

Is there any other way to look and troubleshoot more about this?

Thanks,

[ITOMB_IMT]

By seeing the posts i adjusted the firewall rules and it worked but when i tried to login, i see these
SQL Error [nagiosxi] : Table 'nagiosxi.xi_options' doesn't exist

I ran /usr/local/nagiosxi/scripts/repair_databases.sh, i see these

/usr/local/nagiosxi/scripts/repair_databases.sh
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
DATABASE: nagios
TABLE:
Issued remote command 'mysqlcheck -f -r -u nagios -pnagios -h DB_server --port=3306 --databases nagios'
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
DATABASE: nagiosql
TABLE:
Issued remote command 'mysqlcheck -f -r -u nagiosql -pnagiosql -h DB_server --port=3306 --databases nagiosql'
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
DATABASE: nagiosxi
TABLE:
nagiosxi.xi_incidents
note : The storage engine for the table doesn't support repair
Issued remote command 'mysqlcheck -f -r -u nagiosxi -pnagiosxi -h DB_server --port=3306 --databases nagiosxi'
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory

=======================
nagios offloaded database repair succeeded
nagiosql offloaded database repair succeeded
nagiosxi offloaded database repair succeeded

[npolovenko]

Hello, @ISSB_MAOST. Did you update the MariaDB version on the offloaded server prior to the issue or install any security patches?

Please sign in to the remote DB server, run these commands and show me the output:

mysql -V

mysqlcheck -r -f -uroot -pnagiosxi --all-databases --use_frm

[ITOMB_IMT]

Hello, @ISSB_MAOST. Did you update the MariaDB version on the offloaded server prior to the issue or install any security patches?
we had a OS upgrade from RHEL 7.3 to 7.5.

Please sign in to the remote DB server, run these commands and show me the output:

mysql -V

mysql -V
mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

mysqlcheck -r -f -uroot -pnagiosxi --all-databases --use_frm

mysqlcheck -r -f -uroot -proot --all-databases --use_frm
mysql.columns_priv OK
mysql.db
warning : Number of rows changed from 0 to 11
status : OK
mysql.event OK
mysql.func OK
mysql.help_category
warning : Number of rows changed from 0 to 39
status : OK
mysql.help_keyword
warning : Number of rows changed from 0 to 464
status : OK
mysql.help_relation
warning : Number of rows changed from 0 to 1028
status : OK
mysql.help_topic
warning : Number of rows changed from 0 to 508
status : OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv
warning : Number of rows changed from 0 to 2
status : OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user
warning : Number of rows changed from 0 to 20
status : OK
nagios.nagios_acknowledgements
warning : Number of rows changed from 0 to 8
status : OK
nagios.nagios_commands
warning : Number of rows changed from 0 to 127
status : OK
nagios.nagios_commenthistory
warning : Number of rows changed from 0 to 112802
status : OK
nagios.nagios_comments
warning : Number of rows changed from 0 to 218
status : OK
nagios.nagios_configfiles
warning : Number of rows changed from 0 to 1
status : OK
nagios.nagios_configfilevariables
warning : Number of rows changed from 0 to 132
status : OK
nagios.nagios_conninfo
warning : Number of rows changed from 0 to 2274
status : OK
nagios.nagios_contact_addresses OK
nagios.nagios_contact_notificationcommands
warning : Number of rows changed from 0 to 228
status : OK
nagios.nagios_contactgroup_members
warning : Number of rows changed from 0 to 19
status : OK
nagios.nagios_contactgroups
warning : Number of rows changed from 0 to 4
status : OK
nagios.nagios_contactnotificationmethods
info : Delete link points outside datafile at 6108
warning : Number of rows changed from 0 to 9272
status : OK
nagios.nagios_contactnotifications
warning : Number of rows changed from 0 to 9271
status : OK
nagios.nagios_contacts
warning : Number of rows changed from 0 to 19
status : OK
nagios.nagios_contactstatus
warning : Number of rows changed from 0 to 19
status : OK
nagios.nagios_customvariables
warning : Number of rows changed from 0 to 819
status : OK
nagios.nagios_customvariablestatus
warning : Number of rows changed from 0 to 819
status : OK
nagios.nagios_dbversion
warning : Number of rows changed from 0 to 1
status : OK
nagios.nagios_downtimehistory
warning : Number of rows changed from 0 to 871
status : OK
nagios.nagios_eventhandlers
info : Delete link points outside datafile at 0
info : Found block that points outside data file at 632
info : Found block that points outside data file at 644
status : OK
nagios.nagios_externalcommands
warning : Number of rows changed from 0 to 2
status : OK
nagios.nagios_flappinghistory
warning : Number of rows changed from 0 to 313
status : OK
nagios.nagios_host_contactgroups
warning : Number of rows changed from 0 to 20
status : OK
nagios.nagios_host_contacts
warning : Number of rows changed from 0 to 45
status : OK
nagios.nagios_host_parenthosts OK
nagios.nagios_hostchecks OK
nagios.nagios_hostdependencies OK
nagios.nagios_hostescalation_contactgroups OK
nagios.nagios_hostescalation_contacts OK
nagios.nagios_hostescalations OK
nagios.nagios_hostgroup_members
warning : Number of rows changed from 0 to 15
status : OK
nagios.nagios_hostgroups
warning : Number of rows changed from 0 to 16
status : OK
nagios.nagios_hosts
warning : Number of rows changed from 0 to 43
status : OK
nagios.nagios_hoststatus
info : Delete link points outside datafile at 13968
warning : Number of rows changed from 0 to 43
status : OK
nagios.nagios_instances
warning : Number of rows changed from 0 to 1
status : OK
nagios.nagios_logentries
info : Delete link points outside datafile at 969884
info : Delete link points outside datafile at 971952
warning : Number of rows changed from 0 to 280490
status : OK
nagios.nagios_notifications
info : Delete link points outside datafile at 16096
warning : Number of rows changed from 0 to 10122
status : OK
nagios.nagios_objects
warning : Number of rows changed from 0 to 9358
status : OK
nagios.nagios_processevents
warning : Number of rows changed from 0 to 8683
status : OK
nagios.nagios_programstatus
warning : Number of rows changed from 0 to 1
status : OK
nagios.nagios_runtimevariables
warning : Number of rows changed from 0 to 18
status : OK
nagios.nagios_scheduleddowntime
warning : Number of rows changed from 0 to 217
status : OK
nagios.nagios_service_contactgroups
warning : Number of rows changed from 0 to 393
status : OK
nagios.nagios_service_contacts
warning : Number of rows changed from 0 to 783
status : OK
nagios.nagios_service_parentservices OK
nagios.nagios_servicechecks OK
nagios.nagios_servicedependencies OK
nagios.nagios_serviceescalation_contactgroups OK
nagios.nagios_serviceescalation_contacts OK
nagios.nagios_serviceescalations OK
nagios.nagios_servicegroup_members OK
nagios.nagios_servicegroups OK
nagios.nagios_services
warning : Number of rows changed from 0 to 778
status : OK
nagios.nagios_servicestatus
info : Delete link points outside datafile at 249900
info : Found block that points outside data file at 250252
info : Found block that points outside data file at 250292
info : Found block that points outside data file at 250496
info : Found block that points outside data file at 250536
info : Found block that points outside data file at 251152
info : Found block that points outside data file at 251708
info : Found block that points outside data file at 251800
info : Found block that points outside data file at 252216
info : Found block that points outside data file at 252548
info : Found block that points outside data file at 253004
info : Found block that points outside data file at 253024
info : Found block that points outside data file at 253044
info : Found block that points outside data file at 253332
info : Found block that points outside data file at 253596
warning : Number of rows changed from 0 to 778
status : OK
nagios.nagios_statehistory
warning : Number of rows changed from 0 to 10395
status : OK
nagios.nagios_systemcommands
info : Delete link points outside datafile at 0
info : Delete link points outside datafile at 440
info : Found block with too small length at 1460; Skipped
info : Found block that points outside data file at 3408
info : Found block with too small length at 3524; Skipped
info : Found block with too small length at 5592; Skipped
info : Delete link points outside datafile at 23620
warning : Number of rows changed from 0 to 108
status : OK
nagios.nagios_timedeventqueue OK
nagios.nagios_timedevents OK
nagios.nagios_timeperiod_timeranges
warning : Number of rows changed from 0 to 138
status : OK
nagios.nagios_timeperiods
warning : Number of rows changed from 0 to 23
status : OK
nagiosql.tbl_command
Warning : Can't open table
status : Operation failed
nagiosql.tbl_contact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_contactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_contacttemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_domain
Warning : Can't open table
status : Operation failed
nagiosql.tbl_host
Warning : Can't open table
status : Operation failed
nagiosql.tbl_hostdependency
Warning : Can't open table
status : Operation failed
nagiosql.tbl_hostescalation
Warning : Can't open table
status : Operation failed
nagiosql.tbl_hostextinfo
Warning : Can't open table
status : Operation failed
nagiosql.tbl_hostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_hosttemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_info
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactToCommandHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactToCommandService
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactToContacttemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactToVariabledefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactgroupToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContactgroupToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContacttemplateToCommandHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContacttemplateToCommandService
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContacttemplateToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContacttemplateToContacttemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkContacttemplateToVariabledefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostToHosttemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostToVariabledefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostdependencyToHost_DH
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostdependencyToHost_H
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostdependencyToHostgroup_DH
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostdependencyToHostgroup_H
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostescalationToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostescalationToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostescalationToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostescalationToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostgroupToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHostgroupToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHosttemplateToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHosttemplateToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHosttemplateToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHosttemplateToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHosttemplateToHosttemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkHosttemplateToVariabledefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToServicegroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToServicetemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceToVariabledefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicedependencyToHost_DH
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicedependencyToHost_H
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicedependencyToHostgroup_DH
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicedependencyToHostgroup_H
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicedependencyToService_DS
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicedependencyToService_S
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceescalationToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceescalationToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceescalationToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceescalationToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServiceescalationToService
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicegroupToService
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicegroupToServicegroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToContact
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToContactgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToHost
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToHostgroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToServicegroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToServicetemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkServicetemplateToVariabledefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_lnkTimeperiodToTimeperiod
Warning : Can't open table
status : Operation failed
nagiosql.tbl_logbook
Warning : Can't open table
status : Operation failed
nagiosql.tbl_mainmenu
Warning : Can't open table
status : Operation failed
nagiosql.tbl_permission
Warning : Can't open table
status : Operation failed
nagiosql.tbl_permission_inactive
Warning : Can't open table
status : Operation failed
nagiosql.tbl_service
Warning : Can't open table
status : Operation failed
nagiosql.tbl_servicedependency
Warning : Can't open table
status : Operation failed
nagiosql.tbl_serviceescalation
Warning : Can't open table
status : Operation failed
nagiosql.tbl_serviceextinfo
Warning : Can't open table
status : Operation failed
nagiosql.tbl_servicegroup
Warning : Can't open table
status : Operation failed
nagiosql.tbl_servicetemplate
Warning : Can't open table
status : Operation failed
nagiosql.tbl_session
Warning : Can't open table
status : Operation failed
nagiosql.tbl_session_locks
Warning : Can't open table
status : Operation failed
nagiosql.tbl_settings
Warning : Can't open table
status : Operation failed
nagiosql.tbl_submenu
Warning : Can't open table
status : Operation failed
nagiosql.tbl_timedefinition
Warning : Can't open table
status : Operation failed
nagiosql.tbl_timeperiod
Warning : Can't open table
status : Operation failed
nagiosql.tbl_user
Warning : Can't open table
status : Operation failed
nagiosql.tbl_variabledefinition
Warning : Can't open table
status : Operation failed
nagiosxi.xi_auditlog
note : The storage engine for the table doesn't support repair
nagiosxi.xi_auth_tokens
note : The storage engine for the table doesn't support repair
nagiosxi.xi_cmp_trapdata
note : The storage engine for the table doesn't support repair
nagiosxi.xi_cmp_trapdata_log
note : The storage engine for the table doesn't support repair
nagiosxi.xi_commands
note : The storage engine for the table doesn't support repair
nagiosxi.xi_eventqueue
note : The storage engine for the table doesn't support repair
nagiosxi.xi_events
note : The storage engine for the table doesn't support repair
nagiosxi.xi_incidents
note : The storage engine for the table doesn't support repair
nagiosxi.xi_meta
note : The storage engine for the table doesn't support repair
nagiosxi.xi_options
note : The storage engine for the table doesn't support repair
nagiosxi.xi_sessions
note : The storage engine for the table doesn't support repair
nagiosxi.xi_sysstat
note : The storage engine for the table doesn't support repair
nagiosxi.xi_usermeta
note : The storage engine for the table doesn't support repair
nagiosxi.xi_users
note : The storage engine for the table doesn't support repair

Now i can log into Nagios UI without any error, i can see all of my hosts in Home dashboard, where as i cannot see them in CCM.

I can see all the tables and data in the databases. I got session locks table doesn't exist error in UI where as i can see that table in that nagiosql database.

See the screenshots.

[npolovenko]

@ISSB_MAOST, Looks like MySQL can't open tables and do the repair. It could be a permissions problem or corrupted tables that can no longer be repaired. When did this issue start? Can you sign in to the remote MySQL server and show me the output of the following command?

df -h

Please upload mysql/mariadb log from the remote server.

/var/log/mariadb/mariadb.log
/var/log/mysqld.log

Also, please check whether stict trans table option is enabled:

https://support.nagios.com/kb/article/n ... s-780.html

[ITOMB_IMT]

@ISSB_MAOST, Looks like MySQL can't open tables and do the repair. It could be a permissions problem or corrupted tables that can no longer be repaired. When did this issue start? Can you sign in to the remote MySQL server and show me the output of the following command?

df -h

Please upload mysql/mariadb log from the remote server.

/var/log/mariadb/mariadb.log
/var/log/mysqld.log

Also, please check whether stict trans table option is enabled:

https://support.nagios.com/kb/article/n ... s-780.html

It happened since yesterday.

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/sysvg-lv_root 25G 85M 25G 1% /
devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs 3.9G 796K 3.9G 1% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/sysvg-lv_usr 10G 4.2G 5.9G 42% /usr
/dev/mapper/sysvg-lv_opt 25G 1.4G 24G 6% /opt
/dev/mapper/sysvg-lv_home 10G 33M 10G 1% /home
/dev/mapper/sysvg-lv_store 40G 104M 40G 1% /store
/dev/mapper/sysvg-lv_tmp 15G 36M 15G 1% /tmp
/dev/mapper/sysvg-lv_var 8.0G 1.5G 6.6G 18% /var
/dev/mapper/sysvg-lv_mysql 150G 5.0G 146G 4% /var/lib/mysql
/dev/mapper/sysvg-lv_log 30G 21G 9.3G 69% /var/log
/dev/mapper/sysvg-lv_journal 8.0G 961M 7.1G 12% /var/log/journal

MariaDB [(none)]> use nagiosql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [nagiosql]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE\G;
*************************** 1. row ***************************
@@SQL_MODE:
@@GLOBAL.SQL_MODE:
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [nagiosql]>

I sent a copy of mariadb.log in PM please have a look.

Thanks,

[npolovenko]

@ISSB_MAOST, Is SElinux running on the remote MYSQL server?

getenforce

Are you using some kind of replication mechanism on the MYSQL? Nagios XI doesn't not work well with DB replication and it is known to cause issues.

From what I'm seeing in the log you sent me, the ibdata1 file disappeared along with some binary mysql log files. That caused the data dictionary to not see the tables.

There was likely a problem during replication and some files went missing.

Do you have any nagios backups that you can restore from? Otherwise, you'd have to do the manual restore or reinstall XI.
https://www.ipserverone.info/database/h ... -ibd-file/

If you reinstall XI, please do not use MYSQL replication as it is not supported and will cause similar issues.