connection.close is destroying other connections in pool

[NekkiNeks]

1. What versions are you using?

Give your database version.

actualy dont know it.

Also run node and show the output of:

process.platform:
win32

process.version:
v16.16.0

process.arch
x64

require('oracledb').versionString
5.4.0

require('oracledb').oracleClientVersionString
21.6.0.0.0

3. Describe the problem

I was created a controller for database, and its works. At the beggining of the app i call DBService.Init(), its creates pool, and then i'am calling DBService.storedProc() to execute Database procedures. It works, but only if i need only one connection at the time. If there is more then one connection, then one closed nonnection (with connection.close()) breaks all other connections with errors NJS-018: "invalid ResultSet" and Error: NJS-003: "invalid connection". What am i doing wrong? because poolMax is 10, and i can make it even bigger, but if there is even two connections opened, then close of first connection broke the second. I am doing it strictly like in documentation and examples but maybe i missed something? You can test it if run many DBService.storedProc() at the time.

PS. It feels like there is many connections is binded to one identifier or something but i can be wrong.

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

i think it must be runnable, but im not shure, because of config.

class DBService {
  private connection!: oracledb.Connection;
  private pool!: oracledb.Pool;
  constructor() {
    try {
      oracledb.fetchAsString = [oracledb.CLOB];
      oracledb.fetchAsBuffer = [oracledb.BLOB];
      oracledb.initOracleClient({ libDir: cfg.db.libDir });
      oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
      oracledb.autoCommit = true;
    } catch (err) {
      console.error("Error INIT Oracle:", err);
      logger.error("Error INIT Oracle:" + JSON.stringify(err));
      process.exit(1);
    }
  }

  async init() {
    try {
      this.pool = await oracledb.createPool({
        user: cfg.db.username,
        password: cfg.db.userpass,
        connectString: `(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ${cfg.db.host})(PORT = ${cfg.db.port}))) (CONNECT_DATA = (SERVICE_NAME = ${cfg.db.servername}) ))`,
        externalAuth: false,
        poolMin: 10,
        poolMax: 10,
        // poolIncrement: 1,
      });
    } catch (err) {
      throw err;
    }
  }

  async storedProc(statement: string, params: any) {
    this.connection = await this.pool.getConnection();

    try {
      this.connection.callTimeout = cfg.db.callTimeout * 1000;

      const result: any = await this.connection.execute(
        statement,
        {
          ...params,
        }
        // { resultSet: true }
      );

      let data = [];
      const resultSet = result.outBinds.cursor;
      data = await resultSet.getRows();
      await resultSet.close();

      return data;
    } catch (err) {
      console.log(err);
      throw err;
    } finally {
      await this.connection.close()
    }
  }
}

[pvenkatraman]

Can you provide the SQL statement? Does it contain any OUT bind parameters?

If you have mentioned {resultSet: true} in options, then you should be picking the resultset from result.resultSet.

There is only one getConnection () & connection.close () -> how do you say other connections are destroyed.

[NekkiNeks]

Pvenkatraman, SQL contain only one out parameter and it is cursor. {Resultset:true} is commented, I forget to delete it. getConnection is create connection for every request, so there is more then one connection. When I’m saying that one connection closes other connections I mean connections in pool.

[cjbj]

@NekkiNeks can you provide a pure JS example (not TS) that does reproduce the problem? What's the error? Have you been able to do some debugging about your connection use, and make sure you aren't using a connection in two places concurrently? Have you confirmed you are only opening the pool once? Are you calling all your own async functions by using await?

[NekkiNeks]

@cjbj I think its not necessary to provide JS example, it works the same, just ignore types when you read my example.

Errors as i mentioned is NJS-018: "invalid ResultSet" and Error: NJS-003: "invalid connection", as i understand first error is "invalid connection" when it tries to close the connection when its already closed (as i mentioned also, its closed by previous connection close, which is async), and the there is "invalid ResultSet" when its tries to resultSet.getRows().

Im not using a connection in two places, because i create it from pool in async function (async storedProc()), work with it in separate stream (as i think) and return promise, so technically i cant use the same connection.

Yes, i am opening pool once, wuth Init().

And yes, im calling functions with await.

[VegarRingdalAibel]

I dont get the code, you create one class holding instance of pool and connection ?

-> create pool -> get connection from pool to this.connection -> if you call storedProc one more time before first gets time to finish, you override this.connection and first connection will close the latest...

If you replace this.connection with const connection this will work/ connection will live until its fisnished/canceled

Or Im I missing something ? It would not be the first time 😂

[NekkiNeks]

@VegarRingdalAibel oh... man, i'am feeling so shamed. Thanks a lot, really didnt saw it for a week.

[VegarRingdalAibel]

@NekkiNeks np, been there, done that 😄

[cjbj]

Thanks @VegarRingdalAibel

@NekkiNeks regarding:

@cjbj I think its not necessary to provide JS example, it works the same

It was necessary for me, because I didn't spot what Vegar saw and I wanted to quickly run your example. I needed it in pure JS.

[NekkiNeks]

@cjbj Okay, next time i will send examples in JS. Thanks again for your time.