Maximum open cursors exceeded

[Marcin]

Hi Victor,

Since upgrading NetXMS server on Solaris from RC version to 2.0.2 I observe following frequently problem:

[24-Feb-2016 10:33:53.520] [ERROR] SQL query failed (Query = "UPDATE raw_dci_values SET raw_value=?,transformed_value=?,last_poll_time=? WHERE item_id=?"): ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
[24-Feb-2016 10:33:53.520] [ERROR] SQL query failed (Query = "INSERT INTO idata_1899 (item_id,idata_timestamp,idata_value) VALUES (?,?,?)"): ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

On the Oracle Db side my observation is following:

      SID SQL_TEXT                                                    OPEN_CURSORS USER_NAME
---------- ------------------------------------------------------------ ------------ ------------------------------
      213 INSERT INTO dct_threshold_conditions (threshold_id,group_id,          299 NETXMSOWNER
      202 INSERT INTO dct_threshold_conditions (threshold_id,group_id,          299 NETXMSOWNER
      203 INSERT INTO dct_threshold_conditions (threshold_id,group_id,          299 NETXMSOWNER
      211 INSERT INTO dct_threshold_conditions (threshold_id,group_id,          299 NETXMSOWNER
      201 INSERT INTO dct_threshold_conditions (threshold_id,group_id,          299 NETXMSOWNER
      203 insert into sys.aud$( sessionid,entryid,statement,ntimestamp            1 NETXMSOWNER
      203 SELECT item_id FROM raw_dci_values WHERE item_id=223                    1 NETXMSOWNER
      203 SELECT threshold_id FROM thresholds WHERE item_id=126                  1 NETXMSOWNER
      202 SELECT threshold_id FROM thresholds WHERE item_id=21                    1 NETXMSOWNER
      202 INSERT INTO dct_thresholds (id,table_id,sequence_number,acti            1 NETXMSOWNER
      203 SELECT threshold_id FROM thresholds WHERE item_id=123                  1 NETXMSOWNER
      203 DELETE FROM dc_table_columns WHERE table_id=:1                          1 NETXMSOWNER
      203 SELECT item_id FROM raw_dci_values WHERE item_id=222                    1 NETXMSOWNER
      202 DELETE FROM dci_schedules WHERE item_id=9                              1 NETXMSOWNER
      202 DELETE FROM dci_schedules WHERE item_id=48                              1 NETXMSOWNER
      203 UPDATE dc_tables SET node_id=:1,template_id=:2,template_item            1 NETXMSOWNER
      203 DELETE FROM dci_schedules WHERE item_id=121                            1 NETXMSOWNER
      203 DELETE FROM dci_schedules WHERE item_id=218                            1 NETXMSOWNER
      203 UPDATE items SET node_id=:1,template_id=:2,name=:3,source=:4            1 NETXMSOWNER
      203 INSERT INTO dc_table_columns (table_id,sequence_number,colum            1 NETXMSOWNER
      203 SELECT threshold_id FROM thresholds WHERE item_id=219                  1 NETXMSOWNER
      202 SELECT item_id FROM raw_dci_values WHERE item_id=21                    1 NETXMSOWNER
      203 SELECT item_id FROM raw_dci_values WHERE item_id=117                    1 NETXMSOWNER
      202 DELETE FROM dci_schedules WHERE item_id=73                              1 NETXMSOWNER

SQL_FULLTEXT
--------------------------------------------------------------------------------
INSERT INTO dct_threshold_conditions (threshold_id,group_id,sequence_number,column_name,check_operation,check_value) VALUES (:1,:2,:3,:4,:5,:6)

"INSERT INTO dct_threshold_conditions" is opening maximum allowed number of cursors and blocking further operations.
This seems to be cursor memory leak. Application is opening a cursor and not closing it when it's done.

How to solve this issue? This problem is blocking connections (login-in) from Management Console few hours after restart of NetXMS server.

Regards,
Marcin

[Marcin]

Hello,

NetXMS server 2.0.2 is not usable. It's blocked few hours after every restart:

[04-Mar-2016 08:19:47.926] [ERROR] SQL query failed (Query = "INSERT INTO idata_280 (item_id,idata_timestamp,idata_value) VALUES (?,?,?)"): ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

As a result of ORA-01000 it's not possible to login using NetXMS console or if this is possible, other actions are blocked, for example checking details of Alarm (see attached screenshot).

Increasing number open cursors from 300 to 400 does not help.

Best regards,
Marcin

---

[victor]

Hi,

I've fixed the issue. Fix will be included into 2.0.3 release, or I can build patched 2.0.2 for you.

Best regards,
Victor

[Marcin]

Hi Victor,

Thank you very much for fixing the issue.
When release 2.0.3 will be available?
It would be nice to have a patched build for Solaris. We will replace binary file immediately.

As a workaround we disabled most of the DCI where thresholds were used and this slowed down the process of using cursors on dct_threshold_conditions table.
Now NetXMS server is usable for 6-8 hours after restart.

Best regards,
Marcin

[victor]

Hi,

I plan to make 2.0.3 release this Friday. I'll try to build patched binary later today.

Best regards,
Victor

[victor]

Hi,

please try to replace existing libnxcore.so.2.0.0 with this one: https://cloud.netxms.org/index.php/s/XzmBjTnJ0E0zlDV

Best regards,
Victor

[Marcin]

Thank you Victor.
Library was replaced and this solved the problem.

Best regards,
Marcin