mariadb error

[exp-user1]

Hello,

Every time I reboot Nagios server I got the error:
[ERROR] mysqld: Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed

I do the repair by /usr/local/nagiosxi/scripts/repair_databases.sh , then reboot, I have the same error again.

any ideas?

Thanks

[scottwilkerson]

How are you rebooting?

Are you shutting down the system properly from the command line, or just powering off a VM?

You need to properly shutdown from the command line or it would be the equivalent of pulling the power cord out of the wall, and the database is constantly being written to, which will cause crashed tables.

[exp-user1]

Hi scott,

I am just execute "reboot"

regards.

[scottwilkerson]

Can you provide a copy of your mariadb.log so we can see if the proper shutdown (and the stopping of the mariadb service) is occurring before starting after reboot and having crashed tables?

[exp-user1]

kindly find mariadb.log attached, and note that mariadb always causes /tmp to get full.

lsof /tmp
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 48333 mysql 4u REG 253,6 0 1066374 /var/tmp/ibCqW5cx (deleted)
mysqld 48333 mysql 5u REG 253,6 0 1066376 /var/tmp/ib7PB5zV (deleted)
mysqld 48333 mysql 6u REG 253,6 0 1066377 /var/tmp/ib4Mr5Wj (deleted)
mysqld 48333 mysql 7u REG 253,6 0 1066378 /var/tmp/ibVYE1I6 (deleted)
mysqld 48333 mysql 11u REG 253,6 0 1066379 /var/tmp/ibyW8rav (deleted)
mysqld 48333 mysql 55u REG 253,6 1589059277 1066375 /var/tmp/STWAJZv4 (deleted)
mysqld 48333 mysql 60u REG 253,6 1587544064 1066381 /var/tmp/STpOaAw6 (deleted)

How much size is needed on /tmp by mariadb..?

[scottwilkerson]

kindly find mariadb.log attached, and note that mariadb always causes /tmp to get full.

lsof /tmp
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 48333 mysql 4u REG 253,6 0 1066374 /var/tmp/ibCqW5cx (deleted)
mysqld 48333 mysql 5u REG 253,6 0 1066376 /var/tmp/ib7PB5zV (deleted)
mysqld 48333 mysql 6u REG 253,6 0 1066377 /var/tmp/ib4Mr5Wj (deleted)
mysqld 48333 mysql 7u REG 253,6 0 1066378 /var/tmp/ibVYE1I6 (deleted)
mysqld 48333 mysql 11u REG 253,6 0 1066379 /var/tmp/ibyW8rav (deleted)
mysqld 48333 mysql 55u REG 253,6 1589059277 1066375 /var/tmp/STWAJZv4 (deleted)
mysqld 48333 mysql 60u REG 253,6 1587544064 1066381 /var/tmp/STpOaAw6 (deleted)

How much size is needed on /tmp by mariadb..?

This is likely the problem, the amount of disk required depends on the how big your DB tables are.

As an alternative you can change the directory

edit your my.cnf

Code: Select all

tmpdir = /whatever/you/want

Don't forget give write permissions to /whatever/you/want for mysql user

and after that restart mariadb

Code: Select all

systemctl restart mariadb

[scottwilkerson]

It would also be worth seeing what your DB sizes are

Code: Select all

mysql -pnagiosxi

then

Code: Select all

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

And tables in the nagiosxi DB

Code: Select all

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "nagiosxi"
ORDER BY (data_length + index_length) DESC;

[exp-user1]

Is "systemctl restart mariadb" considered as a graceful restart for mysqld?

what is best to restart nagios services gracefully, without rebooting the server. which script is called to do that gracefully?

[scottwilkerson]

Is "systemctl restart mariadb" considered as a graceful restart for mysqld?

Yes, except it will likely have a problem until the /tmp problem is resolved

what is best to restart nagios services gracefully, without rebooting the server. which script is called to do that gracefully?

There are many moving parts, but the services are the following

Code: Select all

systemctl restart nagios
systemctl restart ndo2db
systemctl restart npcd

[exp-user1]

Hi team,

Mysql DB is growing strangely:

last week:
MariaDB [(none)]> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FRO
M information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database | Size (MB) |
+--------------------+-----------+
| aws_dyn_hosts | 0.02 |
| information_schema | 0.07 |
| mysql | 0.63 |
| nagios | 3335.82 |
| nagiosql | 3.75 |
| nagiosxi | 9.98 |
| performance_schema | 0.00 |
+--------------------+-----------+
7 rows in set (4 min 0.08 sec)



today:

MariaDB [(none)]> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database | Size (MB) |
+--------------------+-----------+
| aws_dyn_hosts | 0.02 |
| information_schema | 0.07 |
| mysql | 0.63 |
| nagios | 4490.12 |
| nagiosql | 3.70 |
| nagiosxi | 9.00 |
| performance_schema | 0.00 |
+--------------------+-----------+
7 rows in set (2 min 10.56 sec)


Thanks for quick support..



here is also this strangely big mysql files related to this issue:


ls -lhSr /var/lib/mysql/nagios/ |tail -5
-rw-rw---- 1 mysql mysql 42M Dec 11 13:55 nagios_statehistory.MYD
-rw-rw---- 1 mysql mysql 48M Dec 11 13:55 nagios_notifications.MYD
-rw-rw---- 1 mysql mysql 1.7G Dec 11 14:08 nagios_logentries.MYI
-rw-rw---- 1 mysql mysql 2.7G Dec 11 13:57 nagios_logentries.TMD
-rw-rw---- 1 mysql mysql 2.7G Dec 11 13:55 nagios_logentries.MYD