ResourceRequest timed out #610
Comments
|
+1, and its random. Please any help? |
|
If you're using transactions in your calls it seems to be an issue. Improper usage of them makes pool get stacked. Try to get rid off them. |
|
@glebson1 Thanks for your reply. But, I am not getting how it is affecting to it? |
|
@patriksimek Can you give me any hint that can help? |
|
I got a similar problem with ESOCKETTIMEOUT. It happened when I deploy my app and my docker install the latest version of |
|
@gentlerainsky Thanks. I will try it. |
|
@gentlerainsky I tried by using tedious (v1.x) But still getting resourceRequest timeout issue. |
|
Sequelize fixed this bug. Please just reference their pull request. This is a huge issue that prevents normal operation! |
|
@catcher-in-the-try They did same thing which is already done by some developers in pending PR(coopernurse/node-pool#198 & coopernurse/node-pool#221) of |
|
Guys, Any updates in this? My production is heading to a big event today and I am in a big mess because of this issue. |
|
I had a really hard time on my customer with this too. Managed to reduce the damage somehow with the config below, but its urgent to fix this one. |
|
@labs20 Are you not facing more issue with this configuration? I think 50ms timeout will create this issue more frequently. Also, It will increase chances of this error. |
|
Well, at least on my customer server it seems to have reduced the problem. |
|
@catcher-in-the-try @labs20 It is working fine for me after I used https://github.com/munir131/node-mssql |
|
@munir131 I couldn't figure out how to use, it doesn't seem to be documented or published to NPM. |
|
@catcher-in-the-try |
|
Would be nice to have an official fix of this issue. I encountered the same problem on our production servers and it's pretty annoying... |
|
I ended up doing this:
|
|
@catcher-in-the-try What do you mean by |
|
@catcher-in-the-try How did you accomplish |
|
I fixed this issue by fork https://github.com/coopernurse/node-pool library. It is not looking issue of node-mssql. But, I am not sure. |
|
@munir131 So you just enforce npm to install your forked node-pool fix and it's working now without any |
|
The issue for us was that that the Line 176 in dee9086 This is a problem because of the way the generic node-pool counts how many TDS connections are potentially available. The generic pool adds the number of currently pending promises that the As a result, whenever the The simple fix for this was to just add the following lines to the tedious.js file: I don't think this is the best way to encode this logic but perhaps this will be helpful to other people in this thread |
|
there was an alleged fix for this but i still get the error :( |
|
I'm also getting this error. This affects our production environment rather badly every other 2 days (and the we have to restart our service). Is there any more news on this? |
|
Is this going to be fixed any time soon ? It effects multiple users in theirs productions including mine as well, @patriksimek ? |
|
@yss14 Yes. I pointed to my forked version in which I fixed a generic-pool issue. |
|
@munir131 it is not an issue in generic-pool, it is an issue with node-mssql. I have explained in more detail in my previous comment, and I have submitted a pull request with a simple fix for this. |
upgrade mssql dependency to 4.2.1. mssql package has been updated to resolve an issue related to resource timeout error. Loopback connector is dependent on this package and can face similar issue. tediousjs/node-mssql#610 tediousjs/node-mssql#683
|
Hello, I made some tests (on 4.2.1) and it seems that i cannot have the script recover after the database had errors for more than a while (1 min in my case). I've put at the end of this message the two files with which i tested, and here are the tests and the results i had: Test1 (without setting acquireTimeoutMillis; stop and start db after 10s)
Test2 (without acquireTimeoutMillis; stop and start db after >1min)
Test3 (with acquireTimeoutMillis set to 10000; stop and start db after >1min)
I would expect that in all these 3 types of tests, after the database is back up, to start resolving promises again. How could i achieve this, am i missing something? index.js process
.on('unhandledRejection', (reason, promise) => {
console.error(`unhandled rejection`);
console.error(promise);
console.error(reason);
process.exit(1);
})
.on('uncaughtException', err => {
const msg = `Uncaught exception: ${err.stack}\n exiting...`;
console.error(msg);
process.exit(1);
});
const app = require('./app');
app(3);app.js const sql = require('mssql');
class Repo {
constructor(connectionPool) {
this.connectionPool = connectionPool;
}
async names() {
const pool = await this.connectionPool;
const queryRes = await pool
.request()
.query(
'select name from names'
);
return queryRes['recordset'].map(r => r.name);
}
}
function createConn() {
const user = 'sa';
const password = 'P@55w0rd';
const server = 'localhost';
const database = 'test';
const pool = new sql.ConnectionPool({user, password, server, database, pool: {acquireTimeoutMillis: 10000}});
pool.on('error', err => {
console.error('onPoolError listener');
console.error(err);
});
return pool.connect();
}
module.exports = (
intervalSeconds,
repo = new Repo(createConn())
) => {
const main = () => run().catch(console.error);
main();
setInterval(
main,
intervalSeconds * 1000
);
setInterval(
() => console.log('tick'),
1500
);
async function run() {
await repo.names()
.then(res => console.log(`names res: ${res}`))
.catch(err => console.error(`names err: ${err}`));
}
}; |
|
Opening a new issue, since reopening this one is not possible: #701 |
|
Regarding the fix that was made by @willmorgan for this issue, https://github.com/tediousjs/node-mssql/pull/683/files#diff-740c6ca62b823be279cef39ece34cf7e which rejects the promise for creating the pool. It looks to me that the method poolCreate is called when the connect method is called, so in the beginning. If then the connection was successful, poolCreate will return a resolved promise. And if later there are connection errors, that resolved promise cannot change, since a promise, resolved or rejected, is immutable. |
|
To clarify, my understanding is that That fix works for my team in production. |
|
The way i see it is that you connect when you instantiate the application, and afterwards you issue queries, and don't try to connect again. When you say it's working for your team, can you tell me, is it working in the scenario that the database goes down and recovers? Also, could you please show a simplified code of how you are using it? |
|
If I recall correctly, We're using the pool configuration more or less as per the docs, so there's no special code to show there. Regarding a total database outage, we haven't had one of those, but understanding how pool connection eviction works, as a guess, I'd expect to see |
|
as i interpret the code, poolCreate is called when the ConnectionPool is instantiated. If connect() is successful, it will return a resolved Promise with the ConnectionPool. I cannot see any subsequent calls to connect, or reinstantiation of the ConnectionPool, for instance in query() methods. The issue i encounter is that the library will continue returning timeout RequestError even after the database recovered. Maybe i didnt understand exactly what was the edge case that you tried to fix, can you tell me what edge case(s) you fixed by adding that reject in the poolCreate? |
Yes, because when the connection pool starts it will start to populate the pool with connections. The first connect is there to test if the config is good.
That sounds plausible; my guess is that the mssql library might be trying to run the request through a connection to the database that existed prior to the unavailability. If this is true then you might want to look at custom eviction logic to evict dead connections, or implement a more stringent |
|
Thanks, though I wasn't looking for an explanation on why poolCreate is called when it's called, i wanted to understand what is it that you fixed by adding that reject in there. I managed to "hackfix" it so that it recovers by replacing the whole connection pool when there are errors. I had to do it in a timeout, since doing it immediately would somehow result in no on error handler attached and issueing unhandled rejections. Though this will lose previous issued queries. If someone would want to keep those, a queue would have to sit between the repo method and the connection pool. const sql = require('mssql');
class Repo {
constructor(cfg) {
const self = this;
self.config = cfg;
self.timerNewConn = null;
self.connectionPool = self.newConn();
}
newConn() {
const self = this;
const pool = new sql.ConnectionPool(self.config);
pool.on('error', err => {
console.error('onPoolError listener');
//console.error(err);
if (self.timerNewConn) {
clearTimeout(self.timerNewConn);
}
self.timerNewConn = setTimeout(() => {
pool.close().then(() => self.connectionPool = self.newConn());
}, 7000)
});
return pool.connect(err => {
if (err) {
console.error('onPoolConnectError listener');
console.error(err);
if (self.timerNewConn) {
clearTimeout(self.timerNewConn);
}
self.timerNewConn = setTimeout(() => {
pool.close().then(() => self.connectionPool = self.newConn());
}, 7000)
}
});
}
async names() {
const pool = await this.connectionPool;
const queryRes = await pool
.request()
.query(
'select name from names'
);
return queryRes['recordset'].map(r => r.name);
}
}
function connCfg() {
const user = 'sa';
const password = 'P@55w0rd';
const server = 'localhost';
const database = 'test';
return {user, password, server, database/*, debug: true*/, pool: {
min: 1,
max: 5,
acquireTimeoutMillis: 5000,
evictionRunIntervalMillis: 500
}};
}
module.exports = (
intervalSeconds,
repo = new Repo(connCfg())
) => {
const main = () => run().catch(console.error);
main();
setInterval(
main,
intervalSeconds * 1000
);
async function run() {
console.log("next");
await repo.names()
.then(res => console.log(`names res: ${res}`))
.catch(err => console.error(`names err: ${err.stack}`));
}
}; |
|
Though your solution proposals @willmorgan sound plausible too. |
Cool, good luck. |
I am also facing same error frequently. So I have updated node-mssql version to v4.2.2. I'll keep an eye out for that. |
|
There is a fix in tedious that would be nice to be taken into node-mssql to fix this issue: tediousjs/tedious#769 |
|
Ah sorry, i see it was included in 4.2.2 |
I updated node-mssql version to v4.2.2. But I am facing same error too. Connection timeout. |
|
@LoveMeWithoutAll Can you provide some code that reproduces the error, the config and also details of the environment / platform you are running on, please? |
@dhensby I have encountered connection timeout error even if upgraded node-mssql to v4.2.2. I couldn't reproduce the error immediately. But after running 1~2 days, the error has occured. Getting connection every 1 second may raise the error? I'm speculating so. At now, I changed timing of getting connection to only once at a process lifetime, and keeping an eye out for that.
Error rasing code is below. // ./config/dbConfig.json
{
"user": "-",
"password": "-",
"server": "-",
"port": 0,
"database": "-",
"options": {
"encrypt": false
},
"pool": {
"acquireTimeoutMillis": 15000
}
}// services/db.js
const sql = require("mssql")
const dbConfig = require("../config/dbConfig.json")
const { onDbError } = require("./log")
let connectionPool
sql.on("error", err => {
sql.close()
onDbError(err, "mssql error")
})
const getConnectionPool = async () => {
try {
connectionPool = await new sql.ConnectionPool(dbConfig).connect()
connectionPool.on("error", async err => {
await closeConnectionPool()
onDbError(err, "getConnectionPool error")
})
} catch (err) {
onDbError(err, "getConnectionPool error in catch")
}
}
const closeConnectionPool = async () => {
try {
await connectionPool.close()
} catch (err) {
onDbError(err, "closeConnectionPool error")
}
}
const getPushList = async () => {
try {
let result = await connectionPool.request()
.input("GUBUN", sql.NVarChar, "ENGINE_PUSH")
.input("CONDITION1", sql.NVarChar, "Android")
.input("CONDITION2", sql.NVarChar, engineConfig.runningServer)
.execute("b.dbo.TU_M_PUSH_XMLLIST")
return result.recordset
} catch (err) {
onDbError(err, "getPushList")
return []
}
}// ./server.js
// ...
const dbService = require("./services/db")
// ...
const sendPush = async () => {
await dbService.getConnectionPool()
dbService.getPushList()
.then((msgList) => {
fcmService.sendMsgList(msgList)
})
.catch(async (error) => {
await dbService.closeConnectionPool()
onServerError(error)
})
.finally(async () => {
await dbService.closeConnectionPool()
setTimeout(() => {
sendPush()
}, 1000)
})
}
// ...
sendPush()error log is below |
|
@LoveMeWithoutAll Something about your Can you try changing const getConnectionPool = async () => {
+ if (connectionPool) {
+ return connectionPool;
+ }
try {
connectionPool = await new sql.ConnectionPool(dbConfig).connect()
connectionPool.on("error", async err => {
await closeConnectionPool()
onDbError(err, "getConnectionPool error")
})
} catch (err) {
onDbError(err, "getConnectionPool error in catch")
}
}There's a possibility you're just creating a connection pool every time and eventually hitting the maximum number of connections allowed on the server and so the new pool never connects again. Can you monitor the number of active connections on the server and see if it is constantly increasing or remains stable? |
|
@dhensby Thank you for your guide! I changed code as you recommended. And now 3 different version apps is running on my each server.
Until now, the number of active connections on the all servers are stable. I'm keeping an eye out for that. P.S.
const getConnectionPool = async () => {
try {
+ if (connectionPool && connectionPool.connected) return
connectionPool = await new sql.ConnectionPool(dbConfig).connect()
connectionPool.on("error", async err => {
await closeConnectionPool()
onDbError(err, "getConnectionPool error")
})
} catch (err) {
onDbError(err, "getConnectionPool error in catch")
}
}
SELECT PROGRAM_NAME, HOSTNAME, STATUS, COUNT(DBID) AS NumberOfConnections
FROM sys.sysprocesses
WHERE PROGRAM_NAME = 'node-mssql'
GROUP BY PROGRAM_NAME, HOSTNAME, STATUS |
It shouldn't matter if the connectionPool is connected or not because it could be in the process of connecting when it's called and you'll still be creating many pools that you lose reference to and thus can't close... perhaps you should just un-assign the variable in the catch block? |
@dhensby Thank you for your advice! I have agreed with you. I changed my code. const getConnectionPool = async () => {
try {
+ if (connectionPool) return
connectionPool = await new sql.ConnectionPool(dbConfig).connect()
connectionPool.on("error", async err => {
await closeConnectionPool()
onDbError(err, "getConnectionPool error")
})
} catch (err) {
+ connectionPool = null
onDbError(err, "getConnectionPool error in catch")
}
}
const closeConnectionPool = async () => {
try {
await connectionPool.close()
} catch (err) {
+ connectionPool = null
onDbError(err, "closeConnectionPool error")
}
}And so far, there is no connection problem in my app I am keeping an eye for 4 days! |
|
Fantastic |
|
@dhensby Finally I got a same error |
|
@LoveMeWithoutAll ok, so is it now completely resolved for you by fixing your code? |
|
@dhensby All problems is resolved by restarting app when error occured. Thank you for your kindness!! |
Node: 6.9.1
configuration:
I am getting this error when there is less traffic or there is gap between requests.
The text was updated successfully, but these errors were encountered: