Intermittent error NJS-103: unexpected message type 2 received at position 11 of packet 44

[joseviniciusnunes]

Hello everyone, I have a problem when performing a very large SQL query, the error occurs intermittently, I saw that a problem last year was resolved, would it be the same problem, has it been fixed yet?

I updated to version 6.3.0, but without success, the error occurred intermittently and 90% of the time the same query was identical to the error.

1. What versions are you using? 6.3.0

   Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

   process.platform linux
   process.version v18.17.0
   process.arch x64
   require('oracledb').versionString 6.3.0
   require('oracledb').oracleClientVersionString undefined

2. Is it an error or a hang or a crash? error

3. What error(s) or behavior you are seeing?

 driverError: TypeError: NJS-500: connection to the Oracle Database was broken
  Error recovery failed: NJS-103: unexpected message type 2 received at position 11 of packet 44
  Original error: Cannot read properties of undefined (reading '_oraTypeNum')
      at ExecuteMessage._adjustFetchType (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/messages/withData.js:135:36)
      at ExecuteMessage.processDescribeInfo (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/messages/withData.js:154:14)
      at ExecuteMessage.processMessage (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/messages/withData.js:74:12)
      at ExecuteMessage.process (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/messages/base.js:188:12)
      at ExecuteMessage.decode (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/messages/base.js:179:10)
      at Protocol._decodeMessage (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/protocol.js:78:17)
      at processTicksAndRejections (node:internal/process/task_queues:95:5)
      at Protocol._processMessage (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/protocol/protocol.js:153:9)
      at ThinConnectionImpl._execute (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/connection.js:195:7)
      at ThinConnectionImpl.execute (/home/vinicius/projetos/coamo/api-coft/node_modules/oracledb/lib/thin/connection.js:930:14) {
    code: 'NJS-500'
  },
  code: 'NJS-500'
}

unfortunately I can't paste the query here, it has more than 33000 characters

[anthony-tuininga]

Can you set the environment variable NODE_ORACLEDB_DEBUG_PACKETS to any value and then paste the contents of the packet in question (note it says packet 44 in the error message) and any packets before that that were received by the client? That should help diagnose the issue.

[joseviniciusnunes]

I'm debugging and these IFs seem to be unable to process a message with type 2, which would be TNS_MSG_TYPE_DATA_TYPES

[anthony-tuininga]

The message type (TNS_MSG_TYPE_DATA_TYPES) only occurs during the connection. Are there packets before packet 44 that were received? This is likely a continuation packet and that isn't being stitched together properly. We would need all packets up to packet 44 that were received for this particular request. (The ones before that would say that it was sent to the database and those aren't needed).

[joseviniciusnunes]

ok, log all to .txt file
log.txt

[anthony-tuininga]

Thanks. That is helpful. To make decoding a bit easier can you share the column types that you are fetching?

[joseviniciusnunes]

I can send the data model, would you have an email to send it to? I wouldn't want to expose it here

[anthony-tuininga]

Yes, you can find my e-mail address here: https://pypi.org/project/oracledb/. From my initial decode it looks like there are 398 columns being fetched? Is that accurate?

[anthony-tuininga]

No need to send anything. I was able to figure out the source of the issue!

[joseviniciusnunes]

@anthony-tuininga

Cool, is it easy to fix?

Sorry, I wasn't at work to send the information.

[anthony-tuininga]

Yes, I believe so. I've sent a detailed message with what I believe is the solution to the ones who will implement it. The biggest hurdle will be replicating the issue but I have an idea for that, too. Stay tuned!

[sudarshan12s]

Hi @joseviniciusnunes ,
We are kind of able to reproduce same issue you are facing. Can you please try this patch and let us know if it works.

diff --git a/lib/thin/protocol/messages/withData.js b/lib/thin/protocol/messages/withData.js
index 8a73eb1b..cbb0d247 100644
--- a/lib/thin/protocol/messages/withData.js
+++ b/lib/thin/protocol/messages/withData.js
@@ -148,13 +148,15 @@ class MessageWithData extends Message {
       buf.skipUB1();
     }
     resultSet.metadata = [];
+    const metadata = [];
+    const queryVars = [];
     for (let i = 0; i < statement.numQueryVars; i++) {
       const variable = this.processColumnInfo(buf, i + 1);
       if (prevQueryVars && i < prevQueryVars.length) {
         this._adjustFetchType(prevQueryVars[i], variable);
       }
-      statement.queryVars.push(variable);
-      resultSet.metadata.push(variable.fetchInfo);
+      queryVars.push(variable);
+      metadata.push(variable.fetchInfo);
     }
 
     let numBytes = buf.readUB4();
@@ -170,6 +172,8 @@ class MessageWithData extends Message {
       buf.skipBytesChunked();
     }
 
+    resultSet.metadata = metadata;
+    statement.queryVars = queryVars;
     this.resultSetsToSetup.push(resultSet);
   }

[joseviniciusnunes]

good news,

It worked on my development machine, I'm going to move up to the stage environment to perform more tests.

Thanks.

[steeu]

Thanks for the fix. We are struggeling with the same issue and can also confirm that the patch helped.
When will this patch be available in a new release?

[sharadraju]

@steeu This will be available in the upcoming node-oracledb 6.4 release.

[sharadraju]

@steeu @joseviniciusnunes This has been fixed in node-oracledb 6.4
The fix is available here.

[danielsrod]

im using bun version 1.1.4
oracledb bersion 6.4.0

NJS-103: unexpected message type 2 received at position 11 of packet 25
it is something related to this ?

the query is working fine in my sql developer
but when i execute in my app, it crashes

[sudarshan12s]

NJS-103

Can you share the complete packet trace after setting the env,
NODE_ORACLEDB_DEBUG_PACKETS=1

few details on the DB server version used and a minimal program which simulates the issue would help to debug further.