CQN client initiated subscriptions not recovering after DB restart

[stokesr]

1. What versions are you using?

oracledb 5.5.0

Database version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production / Version 19.17.0.0.0

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

Error: DPI-1010: not connected

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

We are getting "Error: DPI-1010: not connected" when attempting to create a CQN subscription following a database restart (using the clientInitiated option). Note that we use the ping mechanism to detect the connection closing. On first connection to the database the subscription is established successfully but following a database restart we see this error (following detection of the connection closing and then establishing a new one).

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

The following recreates the problem. Steps to reproduce:

1) Replace connection + table/sql details 2) Execute program to connect to running database 3) Shutdown database 4) Restart database. On restart the test program will error when subscribing using new connection.

const oracledb = require('oracledb');

const dbConnectionString = '//testserver/testdb?expire_time=1';
let connection = null;

async function getDBConnection() {

  const dbConnectionParams = {
    connectString: dbConnectionString,
    user: 'testuser',
    password: 'testpw',
    events: true
  };

  do {
    try {
      connection = await oracledb.getConnection(dbConnectionParams);
      console.log('Connected!');
    } catch (err) {
      console.log(`Error getting connection to ${dbConnectionString} - ${err.message}`);
      connection = null;
      console.log('Will attempt to reconnect in 5 seconds');
      await sleep(5000);
    }
  } while (!(connection));
}

function subscriptionResponseCallback(message) {
  console.log('Callback triggered!');
}

function getSubscriptionConfig() {

  // Setup the standard config options that apply to all subscriptions
  let subscriptionConfig = {
    clientInitiated: true,
    callback: subscriptionResponseCallback,
    events: true,
    operations: 2,
    sql: 'SELECT acolumn FROM atable',
    subscriptionName: 'asubscription',
    tableName: 'atable',
    qos: oracledb.SUBSCR_QOS_QUERY | oracledb.SUBSCR_QOS_ROWIDS,
  };
  return subscriptionConfig;
}

async function registerSubscriptions() {

  const uniqueSubscriptionName = 'test-subscription';
  const oracleChangeNotificationConfig = getSubscriptionConfig();
  console.log('Registering subscription');
  await connection.subscribe(uniqueSubscriptionName, oracleChangeNotificationConfig);
  console.log('Completed registering subscription');
}

async function checkConnections() {

  try {
    if (connection && connection != null) {
      try {
        await connection.ping();
        console.log('Ping OK');
      } catch (error) {
        console.log(`Ping failed with error ${error} - closing connection`);
        await connection.close(connection);
        connection = null;
      }
    } else {
      // No connection for this connection string so try to connect now
      await getDBConnection();
      await registerSubscriptions();
    }
  } catch (err) {
    console.log(`Error ${err} when checking connection`);
    await connection.close(connection);
    connection = null;
  }
}

async function process() {
  while (true) {
    await checkConnections();
    await sleep(10000);
  }
}

function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

process();

[cjbj]

I looked briefly at this and could reproduce it.

[sharadraju]

@stokesr How are you managing the connections? Do you have a custom pool? Based on the example, the connection seems to be a standalone connection. This error occurs due to the fact that the connection has become invalid on DB restart. Can you try running this use case with a client-side pool, as pools have better connection-management functionalities?

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[rjstokes-ap]

@sharadraju unfortunately we see the same behaviour when using pooled connections. Please let me know if there is any more information you need.

[cjbj]

Can you check if the DB registration was cleaned up after restart? If not, then to cleanup stale/pending registration you can try the steps below.

Find registrations:

  select reg_id, subscription_name, location_name from reg$;

Then for each, execute the code below. For example if the registration id is 604, the subscription name is CHNF604 and location name is OCI:EP:603, then execute:

  declare
      reginfo sys.aq$_reg_info;
      context raw(256);
  begin
      context := null;
      reginfo := sys.aq$_reg_info('CHNF604', DBMS_AQ.NAMESPACE_DBCHANGE, 'OCI:EP:603',context);
      DBMS_AQ.UNREGISTER( sys.aq$_reg_info_list(reginfo),1);
  end;
  /

The CQN team have an open action item to do this more automatically and/or provide a PL/SQL interface you can call.

[cjbj]

@rjstokes-ap it's probably better to register with a standalone connection.

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[sharadraju]

I am closing this issue as it is inactive for long and is an issue with CQN rather than node-oracledb.