Page 1 of 1
polled_data .idb file is missing
Posted: Tue Oct 26, 2021 1:25 pm
by saladij1
Hello,
Our Nagios Fusion server recently ran out of disk space due to the polled_extra database filling up. I believe we have cleared that issue, but now it looks like our polled_data database has gone missing. I am seeing the following at the end of the mariaDB log file:
211026 13:18:43 InnoDB: cannot calculate statistics for table fusion/polled_data
InnoDB: because the .ibd file is missing. For help, please refer to
InnoDB:
http://dev.mysql.com/doc/refman/5.5/en/ ... oting.html
211026 13:18:43 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table fusion/polled_data does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See
http://dev.mysql.com/doc/refman/5.5/en/ ... oting.html
how you can resolve the problem.
I attempted to run the repair_databases.sh script on the appliance, but am now receiving:
./repair_databases.sh
Nagios Fusion
Repair Databases
****************
AUTOMATIC REPAIR UNAVAILABLE!
PLEASE CONTACT NAGIOS SUPPORT STAFF IMMEDIATELY!
Stopping the database...
Stopping the database succeeded.
It is suggested you leave the database in this state until you contact Nagios Support
Any help would be greatly apprecieated.
Thanks!
Re: polled_data .idb file is missing
Posted: Wed Oct 27, 2021 10:12 am
by pbroste
Hello
@saladij1
Let's go ahead and run the database repair from command and let us know how things look after you bounce the services.
Code: Select all
mysqlcheck -u root -pfusion --databases fusion mysql nagiosna
Thanks,
Perry
Re: polled_data .idb file is missing
Posted: Mon Nov 01, 2021 7:34 am
by saladij1
pbroste wrote:Hello
@saladij1
Let's go ahead and run the database repair from command and let us know how things look after you bounce the services.
Code: Select all
mysqlcheck -u root -pfusion --databases fusion mysql nagiosna
Thanks,
Perry
Hello! Thanks for the reply.
I have ran the above command, and bounced the mariaDB and httpd services, but it looks like the issue is still persisting. One thing to note is that I did indeed encounter an error when running the database check, as seen below:
Code: Select all
fusion.auth_tokens OK
fusion.commands OK
fusion.dashboards OK
fusion.dashlets OK
fusion.dashlets_params OK
fusion.log OK
fusion.meta OK
fusion.options OK
fusion.polled_averages OK
fusion.polled_data
Error : Table 'fusion.polled_data' doesn't exist
status : Operation failed
fusion.polled_deltas OK
fusion.polled_extras OK
fusion.polling_lock OK
fusion.servers OK
fusion.sysstat OK
fusion.users OK
fusion.users_servers OK
fusion.users_sessions OK
fusion.views OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv 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 OK
mysqlcheck: Got error: 1049: Unknown database 'nagiosna' when selecting the database
Thanks!
Re: polled_data .idb file is missing
Posted: Mon Nov 01, 2021 10:03 am
by pbroste
Hello
@saladij1
thanks for following up with the details on the database repair. Want to go ahead and get a database dump from you so we can compare tables to see what is going on.
Code: Select all
mysqldump -uroot -pfusion fusion > /tmp/fusiondump.sql
Please pm a copy of the fusiondump.sql.
Thanks,
Perry
Re: polled_data .idb file is missing
Posted: Tue Nov 02, 2021 7:54 am
by saladij1
pbroste wrote:Hello
@saladij1
thanks for following up with the details on the database repair. Want to go ahead and get a database dump from you so we can compare tables to see what is going on.
Code: Select all
mysqldump -uroot -pfusion fusion > /tmp/fusiondump.sql
Please pm a copy of the fusiondump.sql.
Thanks,
Perry
Hello,
I tried to run the command above, but am once again met with an error related to the polled_data table.
Code: Select all
mysqldump: Got error: 1146: "Table 'fusion.polled_data' doesn't exist" when using LOCK TABLES
Do you by chance know if there is any way to recreate this table?
Thanks!
Re: polled_data .idb file is missing
Posted: Tue Nov 02, 2021 12:15 pm
by pbroste
Hello
@saladif1
Thanks for following up; you are correct that the end result will be to re-create the 'fusion.polled_data' table but need to determine the relationship within the database. Let's dig in a bit here and see what we get:
Let's look to see 'polled_data' table lock info:
Code: Select all
echo "show open tables" | mysql -uroot -p fusion
Show status on all tables:
Code: Select all
echo 'show table status;' |mysql -u fusion -pfusion fusion -t
Show status on the table for 'polled_data'
Code: Select all
echo "show table status like 'polled_data'" | mysql -uroot -p fusion
To get the dump, we use these switches to allow us to dump past locks by creating temp with single-transaction and triggers:
Code: Select all
mysqldump --single-transaction --routines --triggers -uroot -pfusion fusion > /tmp/fusiondump.sql
Let's also see the ls on MySQL looks like as well:
Truncate table and then Optimize:
Code: Select all
echo 'TRUNCATE TABLE polled_data;' | mysql -u fusion -pfusion fusion -f
Code: Select all
echo 'OPTIMIZE TABLE polled_data;' | mysql -u fusion -pfusion fusion
Please follow up with the results,
the option to redirect these commands to file output by appending: > /tmp/filename.txt
Thanks,
Perry
Re: polled_data .idb file is missing
Posted: Thu Nov 04, 2021 12:29 pm
by saladij1
Hello @pbroste,
I have sent the requested info in a PM.
Thanks!
Re: polled_data .idb file is missing
Posted: Thu Nov 04, 2021 7:02 pm
by pbroste
Hello
@saladij1
Thanks for following up and send along the info that was requested.
Took a look through the database info and we see that the 'polled_data' table from fusion MySQL is missing. Rebuilt, imported, and repaired the database and will send it via Private Message.
Please go ahead and make a backup of your existing database before importing:
Code: Select all
mysqldump -uroot -pfusion fusion > /tmp/fusiondump.sql
or
Code: Select all
mysqldump --single-transaction --routines --triggers -uroot -pfusion fusion > /tmp/fusiondump.sql
To import the database that we are sending over:
Code: Select all
mysql -uroot -pfusion fusion < /thedirectory/saladif1_polled_data.sql
Thanks,
Perry
Re: polled_data .idb file is missing
Posted: Fri Nov 05, 2021 8:10 am
by saladij1
Perry,
Thank you! Nagios Fusion appears to be functional once again. I appreciate your support on this issue.
Thanks!
Re: polled_data .idb file is missing
Posted: Fri Nov 05, 2021 2:36 pm
by pbroste
Hello
@Saladij1
Excellent, thanks for following, and will go ahead and lock this.
Thanks,
Perry