PHP error with bulk modification tool

[naggyohs]

Hiya,

I've been trying to use the bulk modification tool to change the notification options on 1800~ services. I get a blank page and this in the error logs:

[Thu Apr 25 11:46:22.760601 2024] [php:error] [pid 92149] [client xxx.xxx.xxx.xxx:38470] PHP Fatal error: Uncaught mysqli_sql_exception: 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 ''c','r', `last_modified` = NOW() WHERE `id` IN ('9691','9692','9693','9694','969' at line 1 in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysqli.inc.php:788\nStack trace:\n#0 /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysqli.inc.php(788): mysqli_query()\n#1 /usr/local/nagiosxi/html/db/adodb/adodb.inc.php(1268): ADODB_mysqli->_query()\n#2 /usr/local/nagiosxi/html/db/adodb/adodb.inc.php(1246): ADOConnection->_Execute()\n#3 /usr/local/nagiosxi/html/includes/db.inc.php(569): ADOConnection->Execute()\n#4 /usr/local/nagiosxi/html/includes/components/bulkmodifications/bulkmodifications.inc.php(406): exec_sql_query()\n#5 /usr/local/nagiosxi/html/includes/components/bulkmodifications/step2.php(184): bmt_change_single_config_option()\n#6 /usr/local/nagiosxi/html/includes/components/bulkmodifications/step2.php(27): bmt_process_ccm_bulk_submission()\n#7 {main}\n thrown in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-mysqli.inc.php on line 788, referer: https://nagios.example.com/nagiosxi/inc ... ngleoption

I'm running the latest version of XI (2024R1.1.2) on Centos 7.9.2009. My PHP version is 8.1.28 and my MariaDB is Ver 15.1 Distrib 5.5.68-MariaDB. (x86_64)

I realize that this is likely related to the fact I upgraded from PHP 5.x to 8.x a couple of weeks ago. I also realize that I should've just migrated to a more modern OS release but I had a security auditor to appease...They were not happy to see php 5.x

I have already migrated my XI data to a modern distro but we aren't quite ready to make the cutover, so I figured I'd post here and ask if anyone knows of a way to fix this aside from rolling back or switching to a modern distro. If not, no worries, it isn't worth digging too deep into it. I'll be back in a couple of weeks when I find an issue related to the fact I've just migrated from Centos 7.9 to Ubuntu 22.04... I've learned nothing!

[jmichaelson]

Most likely it is. As Centos 7 is going to be EOL on June 30, I'm glad to see you're testing it in a newer distro of Linux. That was going to be my first suggestion. Can you tell us if you're experiencing this problem on your Ubuntu 22 test system? If not making the cutover might be the easiest solution. Let us know!

[naggyohs]

Thanks for the reply @jmichaelson.

So, the plot thickens...I have this same issue on my migrated version... I'm not sure if this is because I upgraded PHP on centos 7 before migrating to Ubuntu 22.04, or if this is an unrelated issue...Not sure where to go from here. Any suggestions?

[naggyohs]

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 ''c','r',

This part seems suspicious to me. I'm trying to toggle on the (c)ritial, (r)recovery, and (w)arning notification options. It seems to expect another variable due to the (,) but maybe the message is truncated...idk. I don't think that would be related to the PHP upgrade, but I could be wrong. Can anyone else on the latest version test this for me?

[sgardil]

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 ''c','r',

This part seems suspicious to me. I'm trying to toggle on the (c)ritial, (r)recovery, and (w)arning notification options. It seems to expect another variable due to the (,) but maybe the message is truncated...idk. I don't think that would be related to the PHP upgrade, but I could be wrong. Can anyone else on the latest version test this for me?

Hey naggyohs,

It sounds like you already know but we recommend moving distro if you would like to upgrade php rather than upgrading it on an older distro. There are several reasons for this but one large one is it makes sure all users took the same process so we can help debug issues more easily. That aside what option were you running to see this error? I ran the option to change the notification options but wasnt able to get the same error so want to make sure I'm running the same option.

[naggyohs]

Hi sgardil,

I am trying to bulk enable warning, critical, and recovery. I was hoping this was just caused by the PHP upgrade on the old server but it persists on the new OS as well...Possibly because I upgraded PHP on the old server before migrating to the new OS? Although for the new OS I did a clean install then just ran an import so I'm a bit surprised I'm still getting a mysql syntax error... Not sure where to go from here

[naggyohs]

Sorry, I suppose it's also worth noting that when I do the bulk modification tool -> Change single config option -> notification options -> enable warning, critical, recovery, I am using 'Select service using service groups' to select the services. It's also worth noting that I just tested it using 'Enable/Disable passive checks' and was able to perform those actions without error, so the bulk modification tool is not completely broken for me...Just in the one scenario I need the most right now, of course

[sgardil]

Sorry, I suppose it's also worth noting that when I do the bulk modification tool -> Change single config option -> notification options -> enable warning, critical, recovery, I am using 'Select service using service groups' to select the services. It's also worth noting that I just tested it using 'Enable/Disable passive checks' and was able to perform those actions without error, so the bulk modification tool is not completely broken for me...Just in the one scenario I need the most right now, of course

No worries, this is good information to know so I can try to replicate. Unfortunately I am unable to get the same error you are getting, I tested on 2 seperate machines. I am currently installing on cent 7 to see if I can get this error and I will get back if I can replicate it. In the mean time if this cant be replicated it may be best to open up a Support ticket for the case since they will be able to help drill down into whats happening with your machine.

Edit: On a fresh install of my cent 7 with 2024R1.1.2 I wasnt able to replicate the error by creating a service group and updating notification options with the bmt using that service group.

[naggyohs]

Thanks for taking a look, really appreciate it. I noticed yesterday that a service config template file I've been using to bulk generate some configs was adding an extra ',' at the end of the notification options:

notification_options w,c,r,

I went through and corrected all of the services using that format to:

notification_options w,c,r

I'm wondering if that extra comma could have caused the mysql syntax error...Unfortunately, however, the issue persists. Will poke at it a bit more before opening a case with support. We don't pay for it currently and I don't want to have to ask my boss to spring for it over a mistake I might've made

At the very least, I've just gone through with a script and set the notifications options to what I was trying to do via the bulk modification tool... I suppose I can live without that function for now.

[naggyohs]

Also, it only errors if I try to use the bulk modification to set more than one notification option. If I just do warning, etc, it executes, but if I do any combination of two or more options it errors.