Page 1 of 2
Unknown column SQL error and Not Active status
Posted: Tue Jun 11, 2019 6:29 am
by jbpatric
When applying a set of templates to a service group via the bulk modification tool it returned this error for most of the services in the group...
SQL Error [nagiosql] : Unknown column 'servicetemplate_name' in 'field list'
Some of the services applied the change fine.
Now those services that reported an error has a status of Not Applied when looking at the list of services in CCM.
Re: Unknown column SQL error and Not Active status
Posted: Tue Jun 11, 2019 1:33 pm
by cdienger
You may have run into a problem where the config change was too large for the character limit in php.ini. Try increasing it per
https://support.nagios.com/kb/article/n ... e-611.html and then run the bulk tool again.
Re: Unknown column SQL error and Not Active status
Posted: Tue Jun 11, 2019 2:21 pm
by jbpatric
Took the php.ini setting
max_input_vars
from 5000 to 10000, restated Apache. Still getting the same error.
Re: Unknown column SQL error and Not Active status
Posted: Tue Jun 11, 2019 2:56 pm
by ssax
Please PM me a copy of your profile, you can download it from Admin > System Profile > Download Profile.
Your profile will include your XI version so that we can see if there are any bugs in that version related to this.
Run this command (and leave it running):
Code: Select all
tail -F /var/log/httpd/error_log /var/log/httpd/ssl_error_log /usr/local/nagiosxi/var/cmdsubsys.log
Then replicate the issue in the web interface again and after it errors out, please attach the full output from the tail command above so we can debug.
Re: Unknown column SQL error and Not Active status
Posted: Tue Jun 11, 2019 3:33 pm
by jbpatric
I sent over the profile in a PM.
An the log file is attached to this post.
Re: Unknown column SQL error and Not Active status
Posted: Tue Jun 11, 2019 4:07 pm
by ssax
Do this too:
Run this command (and leave it running):
Code: Select all
tail -F /var/log/httpd/error_log /var/log/httpd/ssl_error_log
Then replicate the issue in the web interface again and after it errors out, please attach the full output from the tail command above so we can debug.
Re: Unknown column SQL error and Not Active status
Posted: Wed Jun 12, 2019 6:17 am
by jbpatric
Here's the ssl_error_log file...
Re: Unknown column SQL error and Not Active status
Posted: Wed Jun 12, 2019 7:45 am
by jbpatric
Noticed this morning that when applying more than one template via the bulk mod tool is when I get the SQL error. Applying a single template works fine. I tried different templates, all applied without error as long as I only apply one. I've also tried different combinations of templates when applying more than one, made no difference, still returned the error.
Re: Unknown column SQL error and Not Active status
Posted: Wed Jun 12, 2019 10:23 am
by lmiltchev
I was able to recreate the issue in-house. It should be fixed in the next release of Nagios XI. If you want to fix it sooner though, here's a "workaround":
1. Backup the
/usr/local/nagiosxi/html/includes/components/bulkmodifications/bulkmodifications.inc.php file (just in case):
Code: Select all
cp -p /usr/local/nagiosxi/html/includes/components/bulkmodifications/bulkmodifications.inc.php /usr/local/nagiosxi/html/includes/components/bulkmodifications/bulkmodifications.inc.php.backup
2. Open the original file in a text editor:
Code: Select all
vi /usr/local/nagiosxi/html/includes/components/bulkmodifications/bulkmodifications.inc.php
Change line 121 from this:
Code: Select all
$log = sprintf("Added host template %s to host %s", nagiosql_get_object_name('hosttemplate', $ht), nagiosql_get_host_name($host));
to this:
Code: Select all
$log = sprintf("Added host template %s to host %s", nagiosql_get_object_name('template', $ht, 'hosttemplate'));
and change line 157 from this:
Code: Select all
$log = sprintf("Added service template %s to service %s", nagiosql_get_object_name('servicetemplate', $st), nagiosql_get_service_name($service));
to this:
Code: Select all
$log = sprintf("Added service template %s to service %s", nagiosql_get_object_name('template', $st, 'servicetemplate'));
Save and exit.
3. Make a backup of the
/usr/local/nagiosxi/html/includes/components/nagiosql/nagiosql.inc.php file:
Code: Select all
cp -p /usr/local/nagiosxi/html/includes/components/nagiosql/nagiosql.inc.php /usr/local/nagiosxi/html/includes/components/nagiosql/nagiosql.inc.php.backup
4. Download the file below, unzip it, and place the
nagiosql.inc.php in the
/usr/local/nagiosxi/html/includes/components/nagiosql directory, replacing the original file.
nagiosql.inc.php.zip
5. Test adding multiple templates to services in a service group via the Bulk Mod tool. Did it work now?
Re: Unknown column SQL error and Not Active status
Posted: Tue Jun 18, 2019 5:08 pm
by jbpatric
I applied the changes you sent me and I'm not receiving the SQL error now. Services I hadn't attempted to changes before making the changes applied more than one template error free.
But, the services I modified with the bulk tool before making these changes are still showing a status of "Not Applied". Even if I modify one of these services, not using the bulk tool, the status is not changing. This is only happening on a few servers, should I just remove these entries and re-add?