Unknown column SQL error and Not Active status

[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.

[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.

[jbpatric]

Took the php.ini setting

max_input_vars

from 5000 to 10000, restated Apache. Still getting the same error.

[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.

[jbpatric]

I sent over the profile in a PM.

An the log file is attached to this post.

[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.

[jbpatric]

Here's the ssl_error_log file...

[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.

[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?

[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?