table nagios_notifications crashed (in every hour)

[tgriep]

Edit the /etc/my.cnf file and under the following section

Code: Select all

[mysqld]

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.

Code: Select all

/var/log/mariadb/queries.log

Thanks.

[eycklin]

Hi,

Please see attached query log and worklog.

Thanks,
Eyck Lin

[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

[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

[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

[eycklin]

Hi,

Done.

Please see attached log file.

Thanks,
Eyck Lin

[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

[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

[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

[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.