Page 1 of 2

SQL Error [ndoutils] : Incorrect file format 'nagios_hoststa

Posted: Tue Jan 09, 2018 8:40 am
by lucasnap
Hi,

After updating Nagios XI and CentOS 7 and a reboot I get this error in my dashboard:

Code: Select all

SQL Error [ndoutils] : Incorrect file format 'nagios_hoststatus'
I've tried to repair the database with this command:

Code: Select all

/usr/local/nagiosxi/scripts/repair_databases.sh
Output:

Code: Select all

nagios database repair FAILED, please check output above!
nagiosql database repair succeeded
nagiosxi database repair FAILED, please check output above!
And this command:

Code: Select all

mysqlcheck -r -f -u root -pnagiosxi --all-databases
Output (only the error's):

Code: Select all

nagios.nagios_eventhandlers
Error    : Incorrect file format 'nagios_eventhandlers'
error    : Corrupt
Error    : Incorrect file format 'nagios_hoststatus'
error    : Corrupt
nagios.nagios_instances                            OK
nagios.nagios_logentries
Error    : Incorrect file format 'nagios_logentries'
error    : Corrupt
nagios.nagios_statehistory
Error    : Incorrect file format 'nagios_statehistory'
error    : Corrupt
nagios.nagios_systemcommands
Error    : Incorrect file format 'nagios_systemcommands'
error    : Corrupt
Error    : Incorrect file format 'xi_events'
error    : Corrupt
nagiosxi.xi_incidents
note     : The storage engine for the table doesn't support repair
nagiosxi.xi_meta
Error    : Incorrect file format 'xi_meta'
error    : Corrupt
Extra info:
CentOS 7.4 64bit (it was 7.3)
Nagios XI 5.4.11
SSL enabled (self assigned)
No Gnome or other special software
Running on KVM, installed manually with the ./fullinstall command

Can I still repair this?

Thanks

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Tue Jan 09, 2018 3:19 pm
by dwhitfield
First run

Code: Select all

mysql -u root -pnagiosxi
Then run

Code: Select all

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';
Does that resolve the issue? If not, please attach your /usr/local/nagiosxi/html/config.inc.php

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Wed Jan 10, 2018 5:11 am
by lucasnap
I've run the command's but with no success:

Code: Select all

[root@nagios ~]# mysql -u root -pnagiosxi
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46918
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';
+--------------------------------------------------------------------------+
| CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=MyISAM;') |
+--------------------------------------------------------------------------+
| ALTER TABLE nagiosxi.xi_incidents engine=MyISAM;                         |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)
And my config.inc.php

Code: Select all

<?php
//
// Copyright (c) 2008-2016 Nagios Enterprises, LLC. All rights reserved.
//

// Base url
// - do not include http(s) or host name - this is the base from "http://localhost"
$cfg['base_url'] = "/nagiosxi";

// Base root directory where XI is installed
$cfg['root_dir'] = "/usr/local/nagiosxi";

// Directory where scripts are installed
$cfg['script_dir'] = "/usr/local/nagiosxi/scripts";

$cfg['xidpe_dir'] = '/usr/local/nagios/var/spool/xidpe/';
$cfg['perfdata_spool'] = '/usr/local/nagios/var/spool/perfdata/';

// Nom checkpoints
$cfg['nom_checkpoints_dir'] = "/usr/local/nagiosxi/nom/checkpoints/nagioscore/";

// Force http/https
$cfg['use_https'] = false; // determines whether cron jobs and other scripts will force the use of HTTPS instead of HTTP

// Allow for different http port for subsystem calls
$cfg['port_number'] = false;

// Default server, db, connection settings
$cfg['dbtype'] = ''; // this setting is no longer used - use settings below
$cfg['dbserver'] = 'localhost'; // this setting is no longer used - use settings below

// Database connection type
// 1 = persistent, 0 = normal
$cfg['db_conn_persistent'] = 1;

// DB-specific connection information
$cfg['db_info'] = array(
    "nagiosxi" => array(
        "dbtype" => 'mysql',
        "dbserver" => '',
        "user" => 'nagiosxi',
        "pwd" => 'n@gweb',
        "db" => 'nagiosxi',
        "dbmaint" => array( // variables affecting maintenance of db
            "max_auditlog_age" => 30, // max time (in DAYS) to keep audit log entries
            "max_commands_age" => 480, // max time (minutes) to keep commands
            "max_events_age" => 480, // max time (minutes) to keep events
            "optimize_interval" => 60, // time (in minutes) between db optimization runs
            "repair_interval" => 0, // time (in minutes) between db repair runs
        ),
    ),
    "ndoutils" => array(
        "dbtype" => 'mysql',
        "dbserver" => 'localhost',
        "user" => 'ndoutils',
        "pwd" => 'n@gweb',
        "db" => 'nagios',
        "dbmaint" => array( // variables affecting maintenance of ndoutils db

            "max_externalcommands_age" => 7, // max time (in DAYS) to keep external commands
            "max_logentries_age" => 90, // max time (in DAYS) to keep log entries
            "max_statehistory_age" => 730, // max time (in DAYS) to keep state history information
            "max_notifications_age" => 90, // max time (in DAYS) to keep notifications
            "max_timedevents_age" => 5, // max time (minutes) to keep timed events
            "max_systemcommands_age" => 5, // max time (minutes) to keep system commands
            "max_servicechecks_age" => 5, // max time (minutes) to keep service checks
            "max_hostchecks_age" => 5, // max time (minutes) to keep host checks
            "max_eventhandlers_age" => 5, // max time (minutes) to keep event handlers
            "optimize_interval" => 60, // time (in minutes) between db optimization runs
            "repair_interval" => 0, // time (in minutes) between db repair runs
        ),
    ),
    "nagiosql" => array(
        "dbtype" => 'mysql',
        "dbserver" => 'localhost',
        "user" => 'nagiosql',
        "pwd" => 'n@gweb',
        "db" => 'nagiosql',
        "dbmaint" => array( // variables affecting maintenance of db
            "max_logbook_age" => 480, // max time (minutes) to keep log book records
            "optimize_interval" => 60, // time (in minutes) between db optimization runs
            "repair_interval" => 0, // time (in minutes) between db repair runs
        ),
    ),
);

// db-specific table prefixes
$cfg['db_prefix'] = array(
    "ndoutils" => "nagios_", // prefix for NDOUtils tables
    "nagiosxi" => "xi_", // prefix for XI tables
    "nagiosql" => "tbl_", // prefix for NagiosQL tables
);

// component info
$cfg['component_info'] = array(
    "nagioscore" => array(
        "cgi_dir" => "/usr/local/nagios/sbin",
        "import_dir" => "/usr/local/nagios/etc/import",
        "plugin_dir" => "/usr/local/nagios/libexec",
        "cgi_config_file" => "/usr/local/nagios/etc/cgi.cfg",
        "cmd_file" => "/usr/local/nagios/var/rw/nagios.cmd",
        "log_file" => "/usr/local/nagios/var/nagios.log",
        "nom_checkpoint_interval" => 1440, // time (in minutes) between nom checkpoints
    ),
    "pnp" => array(
        "perfdata_dir" => "/usr/local/nagios/share/perfdata",
        "share_dir" => "/usr/local/nagios/share/pnp",
        "direct_url" => "/nagios/pnp",
        "username" => 'nagiosxi', // don't change this!
        "password" => 'nagiosadmin', // this gets reset when security credentials are reset after installation
    ),
    "perfdata" => array(
        "rrdtool_path" => "/usr/bin/rrdtool",
    ),
    "nagiosql" => array(
        "dir" => "/var/www/html/nagiosql",
        "direct_url" => "/nagiosql",
        "username" => 'nagiosxi', // don't change this!
        "password" => 'n@gweb', // this gets reset when security credentials are reset after installation
    ),
    "nagvis" => array(
        "share_dir" => "/usr/local/nagios/share/nagvis",
        "direct_url" => "/nagios/nagvis",
        "username" => 'nagiosadmin', // don't change this!
        "password" => 'nagiosadmin', // this gets reset when security credentials are reset after installation
    ),
);

$cfg['demo_mode'] = false; // is this in demo mode

$cfg['dashlet_refresh_multiplier'] = 1000; // milliseconds (1 second = 1000)

// REFRESH RATES FOR VARIOUS DASHLETS (IN SECONDS UNLESS THE MULTIPLIER IS CHANGED)
$cfg['dashlet_refresh_rates'] = array(
    "available_updates" => 24 * 60 * 60, // 24 hours
    "systat_eventqueuechart" => 5,
    "sysstat_monitoringstats" => 30,
    "systat_monitoringperf" => 30,
    "sysstat_monitoringproc" => 30,
    "perfdata_chart" => 60, // performance graphs
    "network_outages" => 30,
    "host_status_summary" => 60,
    "service_status_summary" => 60,
    "hostgroup_status_overview" => 60,
    "hostgroup_status_grid" => 60,
    "servicegroup_status_overview" => 60,
    "servicegroup_status_grid" => 60,
    "hostgroup_status_summary" => 60,
    "servicegroup_status_summary" => 60,
    "sysstat_componentstates" => 7,
    "sysstat_serverstats" => 5,
    "network_outages_summary" => 30,
    "network_health" => 30,
    "host_status_tac_summary" => 30,
    "service_status_tac_summary" => 30,
    "feature_status_tac_summary" => 30,
    "admin_tasks" => 60,
    "getting_started" => 60,
    "pagetop_alert_content" => 30, // not a dashlet yet, sits in page header
    "tray_alert" => 30, // sites in page footer
);


// MEMCACHED SETUP
$cfg['memcached_enable'] = false; // should we use memcached or not?
$cfg['memcached_hosts'] = array('127.0.0.1', '192.168.1.3'); // one or more memcached servers
$cfg['memcached_port'] = 11211; // default memcached port
$cfg['memcached_compress'] = false; // use true to store items compressed
$cfg['memcached_ttl'] = 10; // max number of seconds data (from SELECT statements) should be cached


// HTTP BASIC AUTHENTICATION INFO -- USED BY SUBSYSTEM
$cfg['use_basic_authentication'] = false; // is HTTP Basic authentication being used? if so, set the two variables below...
$cfg['subsystem_basic_auth_username'] = 'nagiosxi'; // subsystem credentials
$cfg['subsystem_basic_auth_password'] = 'somepassword';

$cfg['default_language'] = 'en_US'; // default language
$cfg['default_theme'] = ''; // default theme

// available languages
$cfg['languages'] = array(
    "en_US" => "English",
);

/*********   DO NOT MODIFY ANYTHING BELOW THIS LINE   **********/

$cfg['default_instance_id'] = 1; // default ndoutils instance to read from
$cfg['default_result_records'] = 100000; // max number of records to return by default

$cfg['online_help_url'] = "https://support.nagios.com/"; // comment this out to disable online help links
$cfg['feedback_url'] = "https://api.nagios.com/feedback/";
$cfg['privacy_policy_url'] = "https://www.nagios.com/legal/privacypolicy/";

//$cfg['db_version']=101;
$cfg['db_version'] = 113;
//$cfg['product_version']='2009RC1';

$cfg['subsystem_ticket'] = "12345"; // default - this gets reset...

$cfg['htaccess_file'] = "/usr/local/nagiosxi/etc/htpasswd.users";
$cfg['htpasswd_path'] = "/usr/bin/htpasswd";

$cfg['enable_analytics'] = 1;

// Secure notification %responseurl%
// setting this to 1 will force users to login in the response URL
// $cfg['secure_response_url']=1;

// Globally disable in page help system
// $cfg['disable_helpsystem']=1;

///////// keep these in order /////////

if (!defined('CFG_ONLY')) {

// include generic db defs
require_once(dirname(__FILE__) . '/includes/db.inc.php');

// include generic  definitions
require_once(dirname(__FILE__) . '/db/common.inc.php');
Thank you very much for the help.

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Wed Jan 10, 2018 10:31 am
by dwhitfield
Do you have any backups in /store (or elsewhere)? We can certainly continue to troubleshoot this, but I've seen enough of these issues to believe your *quickest* resolution is almost certainly a backup.

Also, are you a customer? If so, you can submit a ticket at https://support.nagios.com/tickets/

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Thu Jan 11, 2018 4:01 am
by lucasnap
Hi,

Yes I have a backups in /store/backup/mysql. Not in nagiosxi.

Would that be sufficient?

I am a customer but only with maintenance, not with support (doh!)

Thanks

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Thu Jan 11, 2018 5:45 pm
by dwhitfield
It should be yes. You should have daily, weekly, and monthly. Just find the one from before the issue started. You'll need to gunzip the .gz file and then echo that file back into the DB. We can help you through that process a bit more tomorrow if need be but our work day is coming to a close and I wanted to make sure I got you a yes/no on your specific question. If you download the nagiosxi installer you can take a look at how we build the initial DB. You're going to do the exact same thing it's just that your file will have more data than the default xi install.

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Sat Jan 13, 2018 5:20 am
by lucasnap
OK, great.

I'll check it out next week, i'll ask questions here when I don't understand.

Thanks a lot.

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Mon Jan 15, 2018 5:19 am
by lucasnap
Hi,

I've found the init-mysql script in the Nagios XI installer and run the import command that I thought would do the trick:

I took a pretty old backup, but the config should be good:

Code: Select all

gunzip /store/backups/mysql/daily/mysql_2017-07-11_07h00m.Tuesday.sql.gz
mysql -pnagiosxi < /store/backups/mysql/daily/mysql/mysql_2017-07-11_07h00m.Tuesday.sql
No error output, but still the same problem.

Thanks for the help.

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Mon Jan 15, 2018 1:53 pm
by tgriep
Try running the mysqlcheck command with the --use_frm option to see if it fixes the MYSQL databases.

Code: Select all

mysqlcheck -r -f -u root -pnagiosxi --use_frm --all-databases
Run it twice and if it repairs the database on the first pass, you should not see the Incorrect file format error the second time it is run.

If you still get the error, run the following commands as root on the Nagios server and post the /tmp/info.txt file.

Code: Select all

df -h >/tmp/info.txt
df -i >>/tmp/info.txt
ls -lR /var/lib/mysql  >>/tmp/info.txt

Re: SQL Error [ndoutils] : Incorrect file format 'nagios_hos

Posted: Tue Jan 16, 2018 8:45 am
by lucasnap
Hi,

This fixed the mysql database! great!

I still have some issues (not able to apply new configuration) but my configuration is back.