Upgrade to 6.0 - Query Processing Issue

[slewis30328]

Recently upgraded to 6 from 5.5 and had an issue with SELECT processing for a query that returns 1,500 rows. Query has approx 30 columns in select and no joins - columns just contain NUMBER and VARCHAR2 types. The query does have an IN clause and 3 (where/and) conditions. Query returns in 1.5 seconds in TOAD client. Inside app, query would just hang - eventually web server thread would drop and call would terminate. I read some other posts about issues with 6 and tried setting fetchArraySize. Initially, that param was not set. I set it to 5, restarted node and re-ran the query in app - result came back in sub-second. Concerned about the impact of this and all downstream effects, I removed the param and rolled back to 5.5.0 and everything was functioning normal again. Before rolling back, I also played with memory allocated on nodejs server and that did not have an impact. I work on a very large - mission critical app - and the DB/Oracle layer plays a major part in every aspect. I don't have the cycles to disect what's going on with this. I would think this would be enough info for you all to figure this out. I have been using this driver for a long time and never had any issues.

oracledb: Upgraded to 6.0.0 from 5.5.0 Oracle: 19.14.0.0.0 Node: 17.4.0 OS: Windows

[cjbj]

@slewis30328 is this Thin or Thick mode? ie. do you call initOracleClient() (to enable Thick mode which uses Instant Client libraries)?

[anthony-tuininga]

Based on the report I think this is a duplicate of #1565 and that thin mode is indeed being used.

@slewis30328, you should be able to upgrade to version 6 if you add a call to initOracleClient() anywhere in your code before you establish your first connection to the database. That should be very similar to version 5.5 with a few minor enhancements. Commenting out that call will enable thin mode. If you can verify that for us we can then close this issue and you can follow along on the other issue. Thanks!

[slewis30328]

I didn't make any modifications to code after I upgraded - never specified thick or thin and I don't call initOracleClient(). Based on the way the docs read, I would be automatically in thin after upgrade. Sounds like if I want to use 6, I need to call initOracleClient() - so thick will be used. I will try this out and let you know. Thanks for quick response.

[cjbj]

Sounds like if I want to use 6, I need to call initOracleClient()

In the short term, that should get you going. But once we can reproduce the issue and fix it, then you should be fine to go back to Thin mode. Note that Thin mode doesn't support some of Oracle's high availability features like Application Continuity and Transparent Application Continuity. Depending on your definition of 'mission critical', and whether you are using these features, you may still want to keep running in Thick mode.

[sudarshan12s]

@slewis30328, For the Query issue you are seeing on thin, is it possible to share logs if its replicated in test environment.

In the test environment, where the issue is reproducible, can you set this environment on the terminal before starting the js file. It would dump packet traces on console (which also includes data(in hex) of the tables ) which can be directed to file, outfile. Can you attach this outfile in the Issue? If you are concerned on data , can you share the last few packets where the error is thrown.

NODE_ORACLEDB_DEBUG_PACKETS

Example on bash shell on mac : (Note for C shell env setting can be done using setenv NODE_ORACLEDB_DEBUG_PACKETS 1)

export NODE_ORACLEDB_DEBUG_PACKETS=1
node fetchMultipleRows.js 2>&1   | tee  outfile

The last few packets in my case:

8088 : 6F 7A 6B 68 39 7A 72 6E |ozkh9zrn|
8096 : 6F 63 77 78 71 68 62 72 |ocwxqhbr|
8104 : 7A 37 30 32 61 66 6F    |z702afo |

2023-05-02 15:49:20.993 Receiving packet:
0000 : 00 00 00 0B 0C 20 00 00 |........|
0008 : 01 00 01                |...     |

2023-05-02 15:49:20.993 Receiving packet:
0000 : 00 00 00 0B 0C 20 00 00 |........|
0008 : 01 00 02                |...     |

2023-05-02 15:49:20.994 Sending packet:
0000 : 00 00 00 0B 0C 00 00 00 |........|
0008 : 01 00 02                |...     |

2023-05-02 15:49:21.287 Receiving packet:
0000 : 00 00 00 54 06 00 00 00 |...T....|
0008 : 20 00 04 01 01 00 00 01 |........|
0016 : 1C 00 00 00 00 03 00 00 |........|
0024 : 00 00 00 00 00 00 00 00 |........|
0032 : 00 00 05 00 00 00 00 00 |........|
0040 : 00 01 1C 00 27 4F 52 41 |....'ORA|
0048 : 2D 30 30 30 32 38 3A 20 |-00028:.|
0056 : 73 65 73 73 69 6F 6E 20 |session.|
0064 : 68 61 73 20 62 65 65 6E |has.been|
0072 : 20 74 65 72 6D 69 6E 61 |.termina|
0080 : 74 65 64 0A             |ted.    |

2023-05-02 15:49:21.289 Sending packet:
0000 : 00 00 00 0A 06 00 00 00 |........|
0008 : 00 40                   |.@      |

Error: NJS-500: connection to the Oracle Database was broken
ORA-00028: session has been terminated
    at Protocol._processMessage (/Users/sudarshs/work/nodejs/node-oracledb/lib/thin/protocol/protocol.js:172:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async ThinConnectionImpl.execute (/Users/sudarshs/work/nodejs/node-oracledb/lib/thin/connection.js:802:7)
    at async Connection.execute (/Users/sudarshs/work/nodejs/node-oracledb/lib/connection.js:860:16)
    at async Connection.<anonymous> (/Users/sudarshs/work/nodejs/node-oracledb/lib/util.js:162:14)
    at async run (/Users/sudarshs/work/nodejs/node-oracledb/examples/fetchMultipleRows.js:126:14) {
  offset: 0,
  errorNum: 28,
  code: 'NJS-500'
}
Error: NJS-500: connection to the Oracle Database was broken
    at Object.throwErr (/Users/sudarshs/work/nodejs/node-oracledb/lib/errors.js:591:10)
    at Protocol._recoverFromError (/Users/sudarshs/work/nodejs/node-oracledb/lib/thin/protocol/protocol.js:132:14)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async Protocol._processMessage (/Users/sudarshs/work/nodejs/node-oracledb/lib/thin/protocol/protocol.js:158:9)
    at async ThinConnectionImpl.close (/Users/sudarshs/work/nodejs/node-oracledb/lib/thin/connection.js:74:9)
    at async Connection.close (/Users/sudarshs/work/nodejs/node-oracledb/lib/connection.js:741:7)
    at async Connection.<anonymous> (/Users/sudarshs/work/nodejs/node-oracledb/lib/util.js:162:14)
    at async run (/Users/sudarshs/work/nodejs/node-oracledb/examples/fetchMultipleRows.js:135:9) {
  code: 'NJS-500'
}
2023-05-02 15:49:21.465 Sending packet:
0000 : 00 00 00 0A 06 00 00 00 |........|
0008 : 00 40                   |.@      |

[anthony-tuininga]

@slewis30328, please try this patch. Thanks to @sudarshan12s, we were able to replicate the issue and resolve it:

diff --git a/lib/thin/protocol/messages/withData.js b/lib/thin/protocol/messages/withData.js
index 72302b77..b1af419b 100644
--- a/lib/thin/protocol/messages/withData.js
+++ b/lib/thin/protocol/messages/withData.js
@@ -582,7 +582,7 @@ class MessageWithData extends Message {
   }

   getBitVector(buf, numBytes) {
-    this.bitVector = buf.readBytes(numBytes);
+    this.bitVector = Buffer.from(buf.readBytes(numBytes));
   }

   processBindParams(buf, params) {

[sharadraju]

The patch in lib/thin/protocol/messages/withData.js for this issue is now available, as indicated by the link above. To apply this patch, simply copy this file to your lib folder and replace the older version. This will be incorporated in the 6.0.2 release.

[slewis30328]

I installed 6.0.1, applied the patch and it resolved the issue. Thanks for your help and quick response.