Reconnect to Oracle not working for queries

[vpeltola]

Environment

Knex version: 0.19.1
Database + version: 12.2.0.1
OS: Fedora 30
@atiertant

Bug

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

If an oracle database is restarted, knex (still) loses the connection and is unable to reconnect in some cases, specifically at least when performing queries.

knex version 0.18 attempted to fix this problem in issue #2608 in this commit
6e5e296

ORA-xxxx error messages were added to the isConnectionError(err) function in knex/lib/dialects/oracle/utils.js file. The function checks if err.message starts with a specific ORA-xxxx or NJS-xxx error message and if so returns true.

However, this does not always work. For example, when attempting to run a query after a database restart, the connection error is not detected, because the err.message string looks like this:

select * from mytable - ORA-03113: end-of-file on communication channel

As stated before, isConnectionError() requires "ORA-03113" to be at the very beginning of the message, which is how it comes out of the oracledb module.

The root of problem seems to be caused by knex/lib/client.js function query(), which prepends the query to err.message (knex version 0.19.1):

158     return this._query(connection, obj).catch((err) => {
159       err.message =
160         this._formatQuery(obj.sql, obj.bindings) + ' - ' + err.message;

If line 160 was reversed so that the original database error message came first, then the isConnectionError() function should work (or even better, don't change err.message at all)

159       err.message =
160         err.message + ' - ' + this._formatQuery(obj.sql, obj.bindings);

[kibertoad]

@vpeltola Would you consider submitting PR with test for this?

[abskmj]

I'm using knex for my project and it seems knex does have intermittent issues reconnecting to the oracle database. I think, the code for oracle dialect could be improved to resolve this.

If you refer the code below, isConnectionError is called only when options.resultSet is set.

knex/lib/dialects/oracledb/index.js

Lines 131 to 145 in f782348

	const fetchAsync = function(sql, bindParams, options, cb) {
	options = options || {};
	options.outFormat = client.driver.OBJECT;
	if (options.resultSet) {
	connection.execute(sql, bindParams || [], options, function(
	err,
	result
	) {
	if (err) {
	if (isConnectionError(err)) {
	connection.close().catch(function(err) {});
	connection.__knex__disposed = err;
	}
	return cb(err);
	}

And it is set only for select statements and not other variants of select statement like .first() or .raw().

knex/lib/dialects/oracledb/index.js

Lines 253 to 255 in f782348

	if (obj.method === 'select') {
	options.resultSet = true;
	}

IMO, resolution will be to move the isConnectionError for all queries through connection.execute()

Moreover, the error codes included are not correct. All oracle error codes starting with ORA are of 5 digits. For example ORA-1012 should be ORA-01012

knex/lib/dialects/oracle/utils.js

Lines 44 to 79 in f782348

	function isConnectionError(err) {
	return [
	'ORA-03114', // not connected to ORACLE
	'ORA-03113', // end-of-file on communication channel
	'ORA-03135', // connection lost contact
	'ORA-12514', // listener does not currently know of service requested in connect descriptor
	'ORA-22', // invalid session ID; access denied
	'ORA-28', // your session has been killed
	'ORA-31', // your session has been marked for kill
	'ORA-45', // your session has been terminated with no replay
	'ORA-378', // buffer pools cannot be created as specified
	'ORA-602', // internal programming exception
	'ORA-603', // ORACLE server session terminated by fatal error
	'ORA-609', // could not attach to incoming connection
	'ORA-1012', // not logged on
	'ORA-1041', // internal error. hostdef extension doesn't exist
	'ORA-1043', // user side memory corruption
	'ORA-1089', // immediate shutdown or close in progress
	'ORA-1092', // ORACLE instance terminated. Disconnection forced
	'ORA-2396', // exceeded maximum idle time, please connect again
	'ORA-3122', // attempt to close ORACLE-side window on user side
	'ORA-12153', // TNS'not connected
	'ORA-12537', // TNS'connection closed
	'ORA-12547', // TNS'lost contact
	'ORA-12570', // TNS'packet reader failure
	'ORA-12583', // TNS'no reader
	'ORA-27146', // post/wait initialization failed
	'ORA-28511', // lost RPC connection
	'ORA-56600', // an illegal OCI function call was issued
	'NJS-040',
	'NJS-024',
	'NJS-003',
	].some(function(prefix) {
	return err.message.indexOf(prefix) === 0;
	});
	}

[ahmad380360]

اعاده. ظبط القارب. وشكرا

[elhigu]

اعاده. ظبط القارب. وشكرا

😸

[abskmj]

@kibertoad can I raise a PR for this ?

[elhigu]

@abskmj this has been tried to be fixed couple of times already some kind of integration test would be nice to be included if you start fixing this.

[abskmj]

I will start with a simple PR which has the right ORA codes. Do I need to create separate issues to resolve this with multiple PRs?

[elhigu]

Thanks! The whole bug should be fixed in a single PR.

[abskmj]

ORA codes are corrected part of PR #3498

[kibertoad]

@vpeltola Could you please try 0.20.1 and see if problem was fixed?

[vpeltola]

Hello!
I just rested this. First, as a sanity check, I tested with knex@0.19.1 + oracledb@4.1.2 which still failed as in the original bug description.
I then updated to knex@0.20.7, re-tested and the problem no longer happens.
Looks like the problem has been corrected. Thank you!