error when adding service to service group

[jh129666]

I get the below error when adding a service to a service group (nagios XI 5.4.12).


Error while inserting data into the database:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE `id`=21' at line 3

[dwhitfield]

What version of mysql or mariadb are you running? You can get this information from /usr/sbin/mysqld --version or something of that nature.

Can you PM me your Profile? You can download it by going to Admin > System Config > System Profile and click the ***Download Profile*** button towards the top. If for whatever reason you *cannot* download the profile, please put the output of View System Info (5.3.4+, Show Profile if older) in the thread (that will at least get us some info). This will give us access to many of the logs we would otherwise ask for individually. If security is a concern, you can unzip the profile take out what you like, and then zip it up again. We may end up needing something you remove, but we can ask for that specifically.

You can also generate a profile manually using the script at /usr/local/nagiosxi/html/includes/components/profile/getprofile.sh

That should generate a profile in /usr/local/nagiosxi/var/components/ which you can get off the server with an application such as FileZilla.

After you PM the profile, please update this thread. Updating this thread is the only way for it to show back up on our dashboard.

If you get an error that PROFILE BUILD FAILED, please see https://support.nagios.com/kb/article.p ... ategory=44

UPDATE: Profile shared with techs

[jh129666]

Here's the version we're running. I sent a PM with the profile.

[nagios@pmonnag002 ~]$ /usr/libexec/mysqld --version
/usr/libexec/mysqld Ver 5.5.52-MariaDB for Linux on x86_64 (MariaDB Server)
180129 20:35:59 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 526 ...

[jh129666]

It seems like there's a limit on the number of services that can be part of a service group as I was able to add 988 services to the group, and the error started occurring when I tried to add number 989.

[dwhitfield]

nagios XI 5.4.12

Was this an upgrade? There was a bug similar to this in a very old version.

That said, it's probably a table limit. In this case, probably tbl_lnkServiceToServicegroup. ***Take a backup!!!***

Code: Select all

mysql -uroot -pnagiosxi nagiosql -e "ALTER TABLE tbl_lnkServiceToServicegroup MODIFY value VARCHAR(10000);"

Please let us know if that does not resolve your issue.

[jh129666]

It was an upgrade from 5.4.11.

Before altering the table, is there a command I can run to see what the current table limit is?

[dwhitfield]

Sure. I think that's the wrong table anyway (it wouldn't have worked because "value" is just a placeholder anyway)

The following should give us the info we need:

Code: Select all

mysql -uroot -pnagiosxi nagiosql -e "describe tbl_servicegroup ;"

[jh129666]

here are the results of that query.

Code: Select all

+----------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                | Type                | Null | Key | Default           | Extra                       |
+----------------------+---------------------+------+-----+-------------------+-----------------------------+
| id                   | int(10) unsigned    | NO   | PRI | NULL              | auto_increment              |
| servicegroup_name    | varchar(255)        | NO   | MUL | NULL              |                             |
| alias                | varchar(255)        | NO   |     | NULL              |                             |
| members              | tinyint(3) unsigned | NO   |     | 0                 |                             |
| servicegroup_members | tinyint(3) unsigned | NO   |     | 0                 |                             |
| notes                | varchar(255)        | YES  |     | NULL              |                             |
| notes_url            | varchar(255)        | YES  |     | NULL              |                             |
| action_url           | varchar(255)        | YES  |     | NULL              |                             |
| active               | enum('0','1')       | NO   |     | 1                 |                             |
| last_modified        | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| access_rights        | varchar(8)          | YES  |     | NULL              |                             |
| config_id            | tinyint(3) unsigned | NO   |     | 0                 |                             |
+----------------------+---------------------+------+-----+-------------------+-----------------------------+

[dwhitfield]

Please run through the following in order and let me know if you run into any issues.

# service nagios stop
# service ndo2db stop
# service mariadb stop
# service crond stop
# service httpd stop
# killall -9 nagios
# killall -9 ndo2db
# rm -f /usr/local/nagios/var/rw/nagios.cmd
# rm -f /usr/local/nagios/var/nagios.lock
# rm -f /usr/local/nagios/var/ndo.sock
# rm -f /usr/local/nagios/var/ndo2db.lock
# rm -f /usr/local/nagiosxi/var/reconfigure_nagios.lock
# for i in `ipcs -q | grep nagios |awk '{print $2}'`; do ipcrm -q $i; done
# service mariadb start
# service ndo2db start
# service nagios start
# service httpd start
# service crond start

Once you've run through those, see if the issue exists. If it does, please send a fresh profile so I can see that that took care of what I am trying to resolve.

[jh129666]

the issue still exists. Sent you a PM with the profile.