knex: Failure to Reconnect to Oracle 12c.

Environment

node: v8.6.0 oracle db: 2.0.15 Knex version: 0.14.2 Database + version: oracle 12c OS: REPL 3.10.0-693.17.1.el7.x86_64 #1 SMP Sun Jan 14 10:36:03 EST 2018 x86_64 x86_64 x86_64 GNU/Linux

Select applicable tempalate from below. If issue is about oracledb support tag @ atiertant. For MSSql tag @ Grimace1975 . Rest of dialects doesn’t need tags.

Bug

Explain what kind of behaviour you are getting and how you think it should do

What I see: Knex does not reconnect after the db bounces.
What I expect: Knex should reconnect after the db bounces.

Error message

Error: ORA-03114: not connected to ORACLE

Reduced test code, for example in https://npm.runkit.com/knex or if it needs real database connection to mysql or postgresql, then single file example which initializes needed data and demonstrates the problem.

My test procedure:

Start the DB.
Run the code below.
Stop the DB.
Once I see the ‘Error: ORA-03113: end-of-file on communication channel’ error.
Wait a few seconds then restart the DB. Once the DB is up I expect to start seeing the SYSDATE again.

'use strict';

var knex = require('knex')({
    client : 'oracledb',
    connection: {
        "user": "user",
        "password": "pwd",
        "database": "sid",
        "host": "host:port"
    },
    debug: false,
    pool: {
        "min": 0,
        "max": 3
    }
});

 function  exitIfDBIsDown() {
    knex.raw('select sysdate from dual').then(resp => {
       console.log(resp);
    }).catch(async err => {
        console.log(err);
    });
}

setInterval(exitIfDBIsDown, 2*1000);

example output:

[ { SYSDATE: 2018-02-27T02:09:24.000Z } ] [ { SYSDATE: 2018-02-27T02:09:25.000Z } ] [ { SYSDATE: 2018-02-27T02:09:27.000Z } ] [ { SYSDATE: 2018-02-27T02:09:29.000Z } ] [ { SYSDATE: 2018-02-27T02:09:31.000Z } ] [ { SYSDATE: 2018-02-27T02:09:33.000Z } ] [ { SYSDATE: 2018-02-27T02:09:35.000Z } ] [ { SYSDATE: 2018-02-27T02:09:37.000Z } ] Error: ORA-03113: end-of-file on communication channel Process ID: 461 Session ID: 139 Serial number: 4295026557 Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE Error: ORA-03114: not connected to ORACLE …

I see the Error: ORA-03114: not connected to ORACLE for the rest of the life of the program. I know the db is running because I’m able to connect to it using sqlplus. Also if I restart the app it connects and once again prints the sysdate.

@atiertant

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 1
Comments: 36 (11 by maintainers)

Most upvoted comments

@elhigu it’s still broken in 0.14.4.
Thanks in advance for working on an oracledb fix.

klfgit on Feb 27, 2018

I’ve been able to implement a workaround for this issue for the time being using a Proxy wrapper. It relies upon on having a simple db.js module that establishes the knex object before exporting it and using it in other modules.

Basically, I’m using the query-error event to trigger a re-establishment of the connection and changing the value of the myDb variable. The Proxy is used to ensure that modules that have already imported db end up using the updated myDb instead of the statically exported one. Definitely not ideal but it seems to work.

// db.js
import config from 'config'
import knex from 'knex'
import logger from './logger';

const myDbDbConfig = config.get('db.myDb')

// eslint-disable-next-line import/no-mutable-exports
let myDb = knex(myDbDbConfig)

myDb.on('query-error', (err) => {
  // Reconnect when we find a 3114 error (until knex issue #2608 is merged)
  if (err.message.indexOf(`ORA-03114`) > -1) {
    logger.error(`Encountered ORA-03114 database connection error. Automatically reconnecting.`)
    myDb = knex(myDbDbConfig)
  }
})

const handler = {
  // Uses the `myDb` variable in the module instead of exported instance which is static
  get: (_target, propKey) => (...args) => myDb[propKey](...args),
}

/*
  Using Proxy to allow us to intercept method calls and pass them
  through the `get` interceptor, which points them towards the non-exported
  `myDb` variable, which will reconnect automatically using the event listener

  https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Proxy
  http://2ality.com/2015/10/intercepting-method-calls.html
*/
const db = new Proxy(myDb, handler)

export default db

And then in my other files:

// other.js
import db from "./db";
// ...
return db.withSchema('foo').select('*')...

icj217 on Jun 18, 2019

I having the same issue with oracle and the error ORA-03114. This is what I discovered:

knex destroy connections that has the flag __knex__disposed /src/client.js#L261
the oracle driver set this flag only for connection errors /src/dialects/oracle/index.js#L148
only ['ORA-12514', 'NJS-040', 'NJS-024', 'NJS-003', 'NJS-024'] are considered connections errors /src/dialects/oracle/index.js#L18

The solution seems to be to add ORA-03114 to the error connection list

jfgodoy on Apr 19, 2018

@elhigu ahahaha, GMTA. Yeah, exactly for that reason I published it as a separate tag so that at least someone could test it.

kibertoad on Apr 12, 2019

~~Just discovered that the idleTimeoutMillis property now has no default value which leads me to experiment this issue.~~

To all those who experiment this issue, ensure that idleTimeoutMillis has been set to at least the previous (and maybe not optimal) default value of 30000. It will give you the previous pool behavior.

Edit: This workaround seems to be no help. Sorry.

beltschatsar on Jul 20, 2018