polled_data .idb file is missing

This support forum board is for questions relating to Nagios Fusion.
Locked
saladij1
Posts: 5
Joined: Fri Apr 09, 2021 8:58 am

polled_data .idb file is missing

Post 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!
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: polled_data .idb file is missing

Post 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
saladij1
Posts: 5
Joined: Fri Apr 09, 2021 8:58 am

Re: polled_data .idb file is missing

Post 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!
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: polled_data .idb file is missing

Post 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
saladij1
Posts: 5
Joined: Fri Apr 09, 2021 8:58 am

Re: polled_data .idb file is missing

Post 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!
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: polled_data .idb file is missing

Post 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:

Code: Select all

ls -lR /var/lib/mysql
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
saladij1
Posts: 5
Joined: Fri Apr 09, 2021 8:58 am

Re: polled_data .idb file is missing

Post by saladij1 »

Hello @pbroste,

I have sent the requested info in a PM.

Thanks!
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: polled_data .idb file is missing

Post 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
saladij1
Posts: 5
Joined: Fri Apr 09, 2021 8:58 am

Re: polled_data .idb file is missing

Post by saladij1 »

Perry,

Thank you! Nagios Fusion appears to be functional once again. I appreciate your support on this issue.

Thanks!
User avatar
pbroste
Posts: 1288
Joined: Tue Jun 01, 2021 1:27 pm

Re: polled_data .idb file is missing

Post by pbroste »

Hello @Saladij1

Excellent, thanks for following, and will go ahead and lock this.

Thanks,
Perry
Locked