Page 3 of 4
Re: table nagios_notifications crashed (in every hour)
Posted: Mon Dec 02, 2019 10:02 am
by tgriep
Edit the /etc/my.cnf file and under the following section
put in the following.
Code: Select all
general-log
general-log-file=/var/log/mariadb/queries.log
Run the following to restart the processes.
Code: Select all
systemctl stop nagios
systemctl stop ndo2db
systemctl stop crond
pkill -9 -u nagios
systemctl restart mariadb
systemctl start ndo2db
systemctl start nagios
systemctl start crond
When the notification table gets corrupted, get the following file and upload it to the post.
Thanks.
Re: table nagios_notifications crashed (in every hour)
Posted: Mon Dec 02, 2019 9:47 pm
by eycklin
Hi,
Please see attached query log and worklog.
Thanks,
Eyck Lin
Re: table nagios_notifications crashed (in every hour)
Posted: Tue Dec 03, 2019 10:37 am
by tgriep
It looks like when the dbmaint script runs and optimizes the table, after that, the table is corrupted so can you get the following file and the archived versions of the that file and post them?
Code: Select all
/usr/local/nagiosxi/var/dbmaint.log
Also, run these commands and post the data.
Code: Select all
echo "SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;" |mysql -t -u root -pnagiosxi
echo "SHOW VARIABLES LIKE 'character\_set\_%';" |mysql -t -u root -pnagiosxi nagios
Thanks
Re: table nagios_notifications crashed (in every hour)
Posted: Wed Dec 04, 2019 5:31 am
by eycklin
Hi,
Please see below log & attached log.
[root@localhost var]# echo "SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;" |mysql -t -u root -pnagiosxi
+--------------------+---------+-------------------+
| database | charset | collation |
+--------------------+---------+-------------------+
| information_schema | utf8 | utf8_general_ci |
| mysql | latin1 | latin1_swedish_ci |
| nagios | utf8 | utf8_general_ci |
| nagiosql | utf8 | utf8_general_ci |
| nagiosxi | latin1 | latin1_swedish_ci |
| performance_schema | utf8 | utf8_general_ci |
| test | latin1 | latin1_swedish_ci |
+--------------------+---------+-------------------+
[root@localhost var]# echo "SHOW VARIABLES LIKE 'character\_set\_%';" |mysql -t -u root -pnagiosxi nagios
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+--------------------------+--------+
Thanks,
Eyck Lin
Re: table nagios_notifications crashed (in every hour)
Posted: Wed Dec 04, 2019 2:17 pm
by tgriep
Login to the XI GUI and go to the Admin > Performance Settings menu and click on the Databases TAB.
Change the Optimize Interval to 1440 and update the settings.
That will change the optimization to once a day instead of every hour.
See if that helps.
Run this as root and post it se we can see the sizes of the MYSQL tables.
Code: Select all
echo "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;" |mysql -t -u root -pnagiosxi
Re: table nagios_notifications crashed (in every hour)
Posted: Wed Dec 04, 2019 8:24 pm
by eycklin
Hi,
Done.
Please see attached log file.
Thanks,
Eyck Lin
Re: table nagios_notifications crashed (in every hour)
Posted: Thu Dec 05, 2019 12:43 am
by eycklin
Hi,
After fix, it doesn't crash from 9:13 till 13:40(now).
We'll still monitor for some days since db optimize process is about once a day now.
Do you need any info other than above log?
If you still need some logs, please let me know.
Thanks,
Eyck Lin
Re: table nagios_notifications crashed (in every hour)
Posted: Thu Dec 05, 2019 10:05 am
by tgriep
Thanks for the files. There are a couple of tables I would like to look at so run the following as root and post the /tmp/info.txt file.
Code: Select all
echo 'select * from nagios_contactnotificationmethods;' |mysql -t -u root -pnagiosxi nagios >/tmp/info.txt
echo 'select * from nagios_contactnotifications;' |mysql -t -u root -pnagiosxi nagios >>/tmp/info.txt
Thanks
Re: table nagios_notifications crashed (in every hour)
Posted: Thu Dec 05, 2019 2:56 pm
by eycklin
Hi,
The file is about 300M(compressed), original 3G(uncompressed).
Please see below links:
https://drive.google.com/file/d/1zfX-x3 ... sp=sharing
And user want to know the root cause of this db crash issue.
Is there anything we can tell user?
Thanks,
Eyck Lin
Re: table nagios_notifications crashed (in every hour)
Posted: Thu Dec 05, 2019 3:55 pm
by tgriep
As of now, I have not found anything to draw a conclusion on what the issue is.
I am just trying to rule out when the Database Optimization happens, that the notifications table gets corrupted.