Page 1 of 2
mariadb error
Posted: Wed Dec 04, 2019 2:21 pm
by 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
Re: mariadb error
Posted: Wed Dec 04, 2019 2:58 pm
by 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.
Re: mariadb error
Posted: Wed Dec 04, 2019 4:03 pm
by exp-user1
Hi scott,
I am just execute "reboot"
regards.
Re: mariadb error
Posted: Wed Dec 04, 2019 4:07 pm
by 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?
Re: mariadb error
Posted: Thu Dec 05, 2019 1:43 pm
by 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..?
Re: mariadb error
Posted: Thu Dec 05, 2019 1:55 pm
by scottwilkerson
exp-user1 wrote: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
Don't forget give write permissions to
/whatever/you/want for mysql user
and after that restart mariadb
Re: mariadb error
Posted: Thu Dec 05, 2019 1:58 pm
by scottwilkerson
It would also be worth seeing what your DB sizes are
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;
Re: mariadb error
Posted: Thu Dec 05, 2019 2:13 pm
by 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?
Re: mariadb error
Posted: Thu Dec 05, 2019 2:28 pm
by scottwilkerson
exp-user1 wrote: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
exp-user1 wrote: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
Re: mariadb error
Posted: Wed Dec 11, 2019 10:54 am
by 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