Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

time out when connect to remote oracle db #186

Closed
coding4smart opened this issue Apr 12, 2019 · 21 comments

Comments

3 participants
@coding4smart
Copy link

commented Apr 12, 2019

Hi

ApplicationInsights:CorrelationIdManager [ { Error: connect ETIMEDOUT ****
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1163:14)
errno: 'ETIMEDOUT',
code: 'ETIMEDOUT',
syscall: 'connect',
address: '****',
port: 443 } ]
Request connection/ConnectRequest received
Request connection/ConnectRequest received
Request connection/ConnectRequest received
Request connection/GetConnectionsRequest received
Request connection/ConnectRequest received
Request connection/ConnectRequest received

Attention

getFunctions not implemented for OracleDB
####################
Request connection/RefreshAllRequest received

Attention

getFunctions not implemented for OracleDB
####################
Error: Error: NJS-040: connection request timeout
at Pool.onRequestTimeout (/Users//.vscode/extensions/mtxr.sqltools-0.17.13/node_modules/oracledb/lib/pool.js:146:38)
at Timeout._onTimeout (/Users//.vscode/extensions/mtxr.sqltools-0.17.13/node_modules/oracledb/lib/pool.js:209:28)
at ontimeout (timers.js:424:11)
at tryOnTimeout (timers.js:288:5)
at listOnTimeout (timers.js:251:5)
at Timer.processTimers (timers.js:211:10)

I'm connecting remote db.Tested the connection works in NodeJS with oracledb module and query returned rows of data.

Thanks

@coding4smart coding4smart added the bug label Apr 12, 2019

@project-bot project-bot bot added this to To do in Kanban Apr 12, 2019

@project-bot project-bot bot moved this from To do to In progress in Kanban Apr 12, 2019

@mtxr mtxr added the oracle label Apr 12, 2019

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 13, 2019

Hi.

  1. Does this timeout error happens every time you execute this query or just from time to time?

  2. Does it work with a really simple query, for ex: select 1 from dual?

Thanks a lot for your answers and for your feedback.

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 14, 2019

Hi

  1. In my case,it never works.

  2. I didn't test 'select 1 from dual',just tested 'select * from a_table';

Thanks

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 14, 2019

Hi

I just tested again minutes ago,it happens when I try to connect to my remote oracle db.

2.I tested the queries in NodeJS again.It works with both select 1 from dual or my other queries.

3.For more information,I'm connecting to the remote oracle db machine(host) with VPN as it's only available for connections through our VPN network.So once connected to VPN,it works in NodeJS to connect to remote oracle db and queries works to return rows.Not sure if SQLTools able to work this kind of network?

Thanks

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 15, 2019

Thanks, I find it difficult to reproduce in my case.

If you restart VS Code, connect to your DB and Run your query, this is the error you immedially get?

@mtxr

This comment has been minimized.

Copy link
Owner

commented Apr 16, 2019

That's interesting and not expected at all. Since we are using raw NodeJS process to handle oracle connections, I'm willing to say we might have a bug, but it's hard to reproduce for sure.

@coding4smart would you mind writing some code in a small repository for us to test?
I'll try to test it as well with other SGDBs trying to follow your pattern.

Following @mickeypearce idea, try to open your VPN connection, open VSCode and then connect. I believe VSCode is sharing env vars with NodeJS connection process and in this case I'm afraid NodeJS is using the wrong route to connect.

Let us know how it goes. Thanks!

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 16, 2019

Hi @mtxr,

I'm also very curious why it's not working in my Mac.So I'm happy to help you testing this issue if writing some codes required.Let me know how to start this.
@mickeypearce,I have restarted my Mac,then connect to VPN,then start VS Code,then connect to the DB.From the Command Palette,I can see 'XXX currently connected' when I click on the Connect icon from the status bar.However in the Connections region of sidebar,still there is nothing under the XXX database icon.When I click on this connection,it shows 'INFO: Executing SQLTools.selectConnection' in the output,and it shows 'INFO: Executing SQLTools.executeQuery' when I select All of a simple SQL as 'select 1 from dual' and go to Command Palette 'SQLTools Connection: Run selected query',and after a few minutes,the error as below displayed:
ERROR: Error: { Error: Request connection/ConnectRequest failed with message: NJS-040: connection request timeout
at handleResponse (/Users/XXX/.vscode/extensions/vscode-sqltools/node_modules/vscode-jsonrpc/lib/main.js:436:1)
at processMessageQueue (/Users/XXX/.vscode/extensions/vscode-sqltools/node_modules/vscode-jsonrpc/lib/main.js:263:1)
at Immediate.module.exports.setImmediate (/Users/XXX/.vscode/extensions/vscode-sqltools/node_modules/vscode-jsonrpc/lib/main.js:247:1)
at runCallback (timers.js:696:18)
at tryOnImmediate (timers.js:667:5)
at processImmediate (timers.js:649:5) code: -32603, data: undefined }
DEBUG: Will run 1 attached handler for 'afterCommandSuccessfullyHooks'
ERROR: Error: { Error: Request connection/ConnectRequest failed with message: NJS-040: connection request timeout
at handleResponse (/Users/XXX/.vscode/extensions/vscode-sqltools/node_modules/vscode-jsonrpc/lib/main.js:436:1)
at processMessageQueue (/Users/XXX/.vscode/extensions/vscode-sqltools/node_modules/vscode-jsonrpc/lib/main.js:263:1)
at Immediate.module.exports.setImmediate (/Users/XXX/.vscode/extensions/vscode-sqltools/node_modules/vscode-jsonrpc/lib/main.js:247:1)
at runCallback (timers.js:696:18)
at tryOnImmediate (timers.js:667:5)
at processImmediate (timers.js:649:5) code: -32603, data: undefined }

will keep trying and following this.

Thanks

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 16, 2019

Thanks. It looks that it fails getting a single connection from the pool...

You mentioned:

2.I tested the queries in NodeJS again.It works with both select 1 from dual or my other queries.

How do you connect to DB in your NodeJS app, do you use node-oracledb library? If yes, can you share connection configuration properties.

SQLTools Oracle uses a connection pool with default settings (for now):

await this.lib.createPool({
connectString,
password: this.credentials.password,
user: this.credentials.username,
poolAlias: this.poolName
});

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 16, 2019

Hi

This is my test in NodeJS and didn't use configuration properties files so far,this piece of code is just for testing the connection works for this issue:

====
1.dbconfig.js:
module.exports = {
user : "XXX",
password : "XXX",
connectString : "rwsXXX.com:1521/SID"
};

2.example.js:
process.env.ORA_SDTZ = 'UTC';

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

async function run() {
let connection;

try {

let sql, binds, options, result;

connection = await oracledb.getConnection(  {
  user          : dbConfig.user,
  password      : dbConfig.password,
  connectString : dbConfig.connectString
});

// Create a table

/** await connection.execute(
  `BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE mytab';
     EXCEPTION
     WHEN OTHERS THEN
       IF SQLCODE NOT IN (-00942) THEN
         RAISE;
       END IF;
   END;`);

await connection.execute(
  `CREATE TABLE mytab (id NUMBER, data VARCHAR2(20))`);*/

// Insert some data

sql = `INSERT INTO mytab VALUES (:1, :2)`;

binds = [ [101, "Alpha" ], [102, "Beta" ], [103, "Gamma" ] ];

// For a complete list of options see the documentation.
options = {
  autoCommit: true,
  // batchErrors: true,  // continue processing even if there are data errors
  bindDefs: [
    { type: oracledb.NUMBER },
    { type: oracledb.STRING, maxSize: 20 }
  ]
};

//result = await connection.executeMany(sql, binds, options);

//console.log("Number of rows inserted:", result.rowsAffected);

// Query the data

sql = `SELECT 1 FROM DUAL`;
binds = {};

// For a complete list of options see the documentation.
options = {
  outFormat: oracledb.OBJECT   // query result format
  // extendedMetaData: true,   // get extra metadata
  // fetchArraySize: 100       // internal buffer allocation size for tuning
};

result = await connection.execute(sql, binds, options);

console.log("Column metadata: ", result.metaData);
console.log("Query results: ");
console.log(result.rows);

// Show the date.  The value of ORA_SDTZ affects the output

sql = `SELECT TO_CHAR(CURRENT_DATE, 'DD-Mon-YYYY HH24:MI') AS CD FROM DUAL`;

//result = await connection.execute(sql, binds, options);
//console.log("Current date query results: ");
//console.log(result.rows[0]['CD']);

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

run();

====
And this is the output of NodeJs:
XXX-Mac:Documents XXX$ node example.js
Column metadata: [ { name: '1' } ]
Query results:
[ { '1': 1 } ]

====
For the connection issue:
1.I tried SQLTools in VS Code,found it doesn't work,VS Code asks to install oradb@3.1.1,then installed it,retry and got the same
2.Hence I got the codes above to test from NodeJS.Sorry this piece of code is not very reading friendly as the purpose is to cover the test of this issue.
3.By the way,I got oracle SQL Developer installed in my Mac and it always works with same remote db as I run it everyday.So I can say SQL Developer and NodeJS and SQLTools of VS Code should got same network environment.

Thanks

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 16, 2019

Thanks again. It is a pool issue appareantly. You getConnection directly in your test, that is difference... hmm

Can you try connecting (in your test) with first creating the pool? 😄

let pool = await oracledb.createPool( {
  user          : dbConfig.user,
  password      : dbConfig.password,
  connectString : dbConfig.connectString
});
connection = await pool.getConnection();

Sorry If I am asking too much... 🙇

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 16, 2019

Hi

I'v tried this piece of code in example.js and it works:

process.env.ORA_SDTZ = 'UTC';

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

async function run() {
let pool = await oracledb.createPool( {
user : dbConfig.user,
password : dbConfig.password,
connectString : dbConfig.connectString
});
let connection;

try {

let sql, binds, options, result;
connection = await pool.getConnection();
/** connection = await oracledb.getConnection(  {
  user          : dbConfig.user,
  password      : dbConfig.password,
  connectString : dbConfig.connectString
});*/

// Create a table

/** await connection.execute(
  `BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE mytab';
     EXCEPTION
     WHEN OTHERS THEN
       IF SQLCODE NOT IN (-00942) THEN
         RAISE;
       END IF;
   END;`);

await connection.execute(
  `CREATE TABLE mytab (id NUMBER, data VARCHAR2(20))`);*/

// Insert some data

sql = `INSERT INTO mytab VALUES (:1, :2)`;

binds = [ [101, "Alpha" ], [102, "Beta" ], [103, "Gamma" ] ];

// For a complete list of options see the documentation.
options = {
  autoCommit: true,
  // batchErrors: true,  // continue processing even if there are data errors
  bindDefs: [
    { type: oracledb.NUMBER },
    { type: oracledb.STRING, maxSize: 20 }
  ]
};

//result = await connection.executeMany(sql, binds, options);

//console.log("Number of rows inserted:", result.rowsAffected);

// Query the data

sql = `SELECT 1 FROM DUAL`;
binds = {};

// For a complete list of options see the documentation.
options = {
  outFormat: oracledb.OBJECT   // query result format
  // extendedMetaData: true,   // get extra metadata
  // fetchArraySize: 100       // internal buffer allocation size for tuning
};

result = await connection.execute(sql, binds, options);

console.log("Column metadata: ", result.metaData);
console.log("Query results: ");
console.log(result.rows);

// Show the date.  The value of ORA_SDTZ affects the output

sql = `SELECT TO_CHAR(CURRENT_DATE, 'DD-Mon-YYYY HH24:MI') AS CD FROM DUAL`;

//result = await connection.execute(sql, binds, options);
//console.log("Current date query results: ");
//console.log(result.rows[0]['CD']);

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

run();

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 16, 2019

Thanks again. Your SQLTools connection settings must be similar to this? If not. please try:

{
    "dialect": "OracleDB",
    "name": "rws",
    "database": "rwsXXX.com:1521/SID",
    "username": "XX",
    "password": "XXX"
}

Important: connectString maps to database property.

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 16, 2019

Hi

The connection Strings got no issue as I checked several times.

I created the connection in SQLTools UI,but not sure in where it stored these info as seems can't edit the existing connections in SQLTools.Only got 2 options for existing connections:Connect or Delete Connection.

Could you let me know where SQLTools stored these connect info?Also how to test the Pool,Connection with SQLTools programatically?

Thanks

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 16, 2019

Any sample codes to test dbPool,db connection with SQLTools api in NodeJS?

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 16, 2019

The settings are stored to "User" or "Workspace" settings. (F1 => Preferences - Open User Settings, F1 => Preferences - Open Workspace Settings)

@coding4smart

This comment has been minimized.

Copy link
Author

commented Apr 17, 2019

Hi

Have found the connection details in settings.json thanks to your comments,and tried to set the connectionTimeout bigger or 0 but got no luck.Also reading some similar threads of timeout issue about node-oracledb from github and stackoverflow.

I got 1 question now is:
As I saw the vscode-sqltools/packages/core/dialect/oracle/index.ts,seems not able to put other parameters like _logStatistic,poolMin,poolMax to create a pool,hence it's rarely able to monitor/manage the pool/connections so far.For this issue could you please let me know how to install SQLTools from binary so that maybe I can debug it,otherwise maybe need more time and efforts to fix this in future.

If this is the case,you can leave it or check it later.

Thanks

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 17, 2019

You are right, pool configuration is minimal at the moment (default settings), but this is also the case in your test nodejs app. The only difference is that SQLTools uses a named pool. (could naming be a problem...)

You execute your test node app in the same environment (via VPN) as you run SQLTools, right? 😄

To debug you will have to first clone the repo:

git clone https://github.com/mtxr/vscode-sqltools.git
cd vscode-sqltools

and then take a look a this doc:
https://github.com/mtxr/vscode-sqltools/blob/master/docs/Contributing/building.md

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 23, 2019

hey @coding4smart , are you still exepriencing the same problem?

@mtxr

This comment has been minimized.

Copy link
Owner

commented Apr 29, 2019

Hey guys, what about this issue?

Does it persist?

@coding4smart

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented Apr 30, 2019

Maybe we should add an optional "dialectOptions" property or something similar to connection settings object that would pass through any dialect specific options to a connection creation. @mtxr @coding4smart what do you think?

Passing poolMin and poolMax for example, or anything else, to a connection:

{
    "dialect": "OracleDB",
    "name": "rws",
    "database": "rwsXXX.com:1521/SID",
    "username": "XX",
    "password": "XXX",
    "dialectOptions": {
      "poolMin": "1",
      "poolMax": "1"
  }
}
@mtxr

This comment has been minimized.

Copy link
Owner

commented Apr 30, 2019

I believe that is the best solution for now since it's an oracle only issue. I would just use the name oracleOptions.

@mickeypearce

This comment has been minimized.

Copy link
Collaborator

commented May 2, 2019

Ok, I will open a PR next week.

@mtxr mtxr closed this May 3, 2019

Kanban automation moved this from In progress to To be released May 3, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.