Page 1 of 1
SQL Errors since migrating install
Posted: Wed Apr 24, 2019 12:28 pm
by GldRush98
Background: This machine was updated from CentOS 6 machine and imported in to a CentOS 7 machine. The CentOS 6 machine was at XI 5.6.0, a backup was performed, then 5.6.0 was clean installed on CentOS 7 machine and the back up was restored. Things seem to be working except for SQL errors I keep seeing on reconfigures or service additions.
I'm seeing these errors any time I reconfigure a service and it's making me nervious there is something wrong with the DB on this system.
Everything seemed to go according procedure during the migration, but obviously something isn't quite right.
If I edit something through CCM I'm not seeing the errors, but editing via the XI interface is producing the errors.
Capture.PNG
Capture2.PNG
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 2:27 pm
by GldRush98
I think it is related to xi_auditlog and the my system should be using psql instead of mariadb... there is a xi_auditlog in mariadb but it hasn't had any new entries since the day I migrated. Mean while there is an xi_auditlog in psql and it seems to have current data in there, both from before and after the day I migrated.
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 2:33 pm
by GldRush98
Ok, it looks like this system does have a psql db and uses it. Config shows:
Code: Select all
$cfg['db_info'] = array(
"nagiosxi" => array(
"dbtype" => 'pgsql',
I think I will follow the psql to Mysql conversion procedure outlined here:
https://support.nagios.com/kb/article/c ... i-560.html
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 2:44 pm
by GldRush98
Getting this trying to import...
Code: Select all
[root@nagios pg2mysql-master]# mysql -uroot -pnagiosxi nagiosxi --max_allowed_packet=512M --force < nagiosxi.mysql
ERROR 1146 (42S02) at line 416: Table 'nagiosxi.xi_mibs' doesn't exist
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 3:05 pm
by GldRush98
Ok, that was a comedy of errors.
I grabbed the 5.6.0 tarball and took the nagiosxi_db.sql file out of there and used that as it had the (new?) xi_mibs table on it. Then I was able to import my converted psql file in and proceed.
Everything appears to be working at this point and those sql errors in my original post are gone.
The file linked in the conversion guide (
https://assets.nagios.com/downloads/nag ... ate_db.sql) should have its sql updated to include the xi_mibs table creation.
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 3:21 pm
by benjaminsmith
Hi
@GldRush98,
It looks like you got it worked out! The error you were seeing was due to a postgres config setting (standard_conforming_strings).
Did you want to keep this post open or shall we close it?
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 3:36 pm
by GldRush98
Ahh, ok. Yeah, you can go ahead and close. Everything is looking good now.
Re: SQL Errors since migrating install
Posted: Wed Apr 24, 2019 3:37 pm
by scottwilkerson
GldRush98 wrote:Ahh, ok. Yeah, you can go ahead and close. Everything is looking good now.
Great
Locking