ORA-03106: fatal two-task communication protocol error

[damianhammond]

1. What versions are you using?
   process.platform: linux
   process.version: v20.11.0
   process.arch: x64
   require('oracledb').versionString: 6.5.1
   Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.28.0.0.0

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

3. What error(s) or behavior you are seeing?
   We are attempting to have oracledb return Buffers as its default but in some cases we need to override and revert back to a stream.
   The original error was an ORA-03106 that popped up in our systems unit tests. I have gotten a variety of behavior depending on thin/thick mode and order of queries. See comments in test script.

4. Include a runnable Node.js script that shows the problem.

/* Copyright (c) 2015, 2024, Oracle and/or its affiliates. */

/******************************************************************************
 *
 * This software is dual-licensed to you under the Universal Permissive License
 * (UPL) 1.0 as shown at https://oss.oracle.com/licenses/upl and Apache License
 * 2.0 as shown at http://www.apache.org/licenses/LICENSE-2.0. You may choose
 * either license.
 *
 * If you elect to accept the software under the Apache License, Version 2.0,
 * the following applies:
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *    https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * NAME
 *   ora03106.js
 *
 * DESCRIPTION
 *   Demonstrate generation of ORA-03106 error. Derived from oracledb example set.
 * 
 *   For this test the intent is to have either lines 137,138 uncommented or
 *   lines 166, 167 uncommented. Not both.
 * 
 *   oracledb: 6.5.1
 *   Node: v20.11.0
 *   Oracle Database: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 *                    Version 19.28.0.0.0
 *
 *****************************************************************************/

'use strict';

Error.stackTraceLimit = 50;

const oracledb = require('oracledb');
const dbConfig = require('./dbconfig.js');

// This example runs in both node-oracledb Thin and Thick modes.
//
// Optionally run in node-oracledb Thick mode
if (process.env.NODE_ORACLEDB_DRIVER_MODE === 'thick') {

  // Thick mode requires Oracle Client or Oracle Instant Client libraries.
  // On Windows and macOS you can specify the directory containing the
  // libraries at runtime or before Node.js starts.  On other platforms (where
  // Oracle libraries are available) the system library search path must always
  // include the Oracle library path before Node.js starts.  If the search path
  // is not correct, you will get a DPI-1047 error.  See the node-oracledb
  // installation documentation.
  let clientOpts = {};
  // On Windows and macOS platforms, set the environment variable
  // NODE_ORACLEDB_CLIENT_LIB_DIR to the Oracle Client library path
  if (process.platform === 'win32' || process.platform === 'darwin') {
    clientOpts = { libDir: process.env.NODE_ORACLEDB_CLIENT_LIB_DIR };
  }
  oracledb.initOracleClient(clientOpts);  // enable node-oracledb Thick mode
}

console.log(oracledb.thin ? 'Running in thin mode' : 'Running in thick mode');


oracledb.fetchAsBuffer = [oracledb.BLOB];

async function run() {

  let connection;

  try {
    connection = await oracledb.getConnection(dbConfig);

    //
    // Create a table
    //

    const stmts = [
      `DROP TABLE no_tab1`,

      `CREATE TABLE no_tab1 (id NUMBER, data BLOB)`
    ];

    for (const s of stmts) {
      try {
        await connection.execute(s);
      } catch (e) {
        if (e.errorNum != 942)
          console.error(e);
      }
    }

    let result = await connection.execute(
      `INSERT INTO no_tab1 VALUES (:id, :data)`,
      { id: {val: 1 }, data: {val: '12345'} }
    );

    var getAsBuffer = async function() {
      result = await connection.execute(
        `SELECT * FROM no_tab1 WHERE id = :id`,
        { id: 1 },
        {outFormat : oracledb.OBJECT,},
      );

      console.log(`Data as Buffer: ${result.rows[0].DATA instanceof Buffer}`);
    }

    var getAsLob = async function() {
      result = await connection.execute(
        `SELECT * FROM no_tab1 WHERE id = :id`,
        { id: 1 },
        {outFormat : oracledb.OBJECT, fetchInfo: {DATA: {type: oracledb.DEFAULT}}},
      );

      console.log(`Data as Lob: ${result.rows[0].DATA instanceof oracledb.Lob}`);
    }


    /*
      Thick Mode Output:
        Running in thick mode
        Data as Lob: true
        Data as Buffer: true

      Thin Mode Output:
        Running in thin mode
        Data as Lob: true
        Data as Buffer: false
    */
    //await getAsLob();
    //await getAsBuffer();


    /*
      Thick Mode Output:
        Running in thick mode
        Data as Buffer: true
        Error: ORA-03106: fatal two-task communication protocol error
            at async getAsLob (/home/damianh/ndev03/spoccode-apex/ora03106.js:108:18)
            at async run (/home/damianh/ndev03/spoccode-apex/ora03106.js:136:5) {
          errorNum: 3106,
          offset: 0,
          code: 'ORA-03106'
        }

      Thin Mode Output:
        Running in thin mode
        Data as Buffer: true
        TypeError: lobImpl.getChunkSize is not a function
            at Lob._setup (/home/damianh/ndev03/spoccode-apex/node_modules/oracledb/lib/lob.js:104:31)
            at ResultSet._processRows (/home/damianh/ndev03/spoccode-apex/node_modules/oracledb/lib/resultset.js:142:15)
            at ResultSet._getAllRows (/home/damianh/ndev03/spoccode-apex/node_modules/oracledb/lib/resultset.js:68:22)
            at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
            at async Connection.execute (/home/damianh/ndev03/spoccode-apex/node_modules/oracledb/lib/connection.js:921:23)
            at async Connection.<anonymous> (/home/damianh/ndev03/spoccode-apex/node_modules/oracledb/lib/util.js:166:14)
            at async getAsLob (/home/damianh/ndev03/spoccode-apex/ora03106.js:108:18)
            at async run (/home/damianh/ndev03/spoccode-apex/ora03106.js:125:5)
    */
    await getAsBuffer();
    await getAsLob();

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

Thanks for your help.

[sharadraju]

HI @damianhammond,
Can you please try with the latest node-oracledb release version 6.9 and let us know the results?

[sudarshan12s]

Hi @damianhammond , the issue happens because you use execute same stmt SELECT * FROM no_tab1 WHERE id = :id but fetch as BLOB once and then as Buffer. This causes issues with statement cache where it remembers the metadata of the columns fetched. We will see if its easier to fix, But one workaround if you have such use case is to skip keeping such stmts in cache.

You can use this option, keepInStmtCache: false , so its like below, Can you confirm your findings..

var getAsBuffer = async function() {
   const result = await connection.execute(
     `SELECT * FROM no_tab1 WHERE id = :id`,
     { id: 1 },
     {outFormat: oracledb.OBJECT, keepInStmtCache: false},
   );
   console.log(`Data as Buffer: ${result.rows[0].DATA instanceof Buffer}`);
 };

 var getAsLob = async function() {
   const result = await connection.execute(
     `SELECT * FROM no_tab1 WHERE id = :id`,
     { id: 1 },
     {outFormat: oracledb.OBJECT, fetchInfo: {DATA: {type: oracledb.DEFAULT}}, keepInStmtCache: false},
   );
   console.log(`Data as Lob: ${result.rows[0].DATA instanceof oracledb.Lob}`);
 };

[cjbj]

@sudarshan12s, this was looked at by @anthony-tuininga last week in relation to oracle/python-oracledb#19 (comment). Another workaround that allows the statement cache to be reused is to make sure the statements differ in each case, e.g by adding a SQL comment to the text

[sosoba]

There was a similar problem for SQL

[damianhammond]

I have put in a comment whenever we specify a column to be returned as a stream. Something similar to

    var getAsLob = async function() {
      result = await connection.execute(
        `SELECT * FROM no_tab1 WHERE id = :id /* as Lob */`,
        { id: 1 },
        {outFormat : oracledb.OBJECT, fetchInfo: {DATA: {type: oracledb.DEFAULT}}},
      );
    
      console.log(`Data as Lob: ${result.rows[0].DATA instanceof oracledb.Lob}`);
    }

This issue we have with using keepInStmtCache: false is that it seems like it is needed on the first execution of the specific sql statement. Around 95% of our sql is auto generated and it is problematic to flag a query that may run into a problem later on in the execution chain.

Thank you for the help and suggestions.