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
Connection Error: Connection is Closed #138
Comments
What version of SQLServer are you using? I had the same problem after upgrading to version 2.1 from 1.3. I have only 2008R2 here to use and test so I'm only speculating that version 2.1 has problems with older SQLServer versions. |
Seeing the same issue since upgrading to 2.1 - Using SQL Azure. |
From the example on the homepage, set encrypt option...
NOTE: you really should be checking your |
Already doing that. |
Is anything in the |
Could you please post some debug data for us? https://github.com/patriksimek/node-mssql/wiki/Debugging-TDS |
Just to add my two cents. I'm getting an error on Azure, though it works locally.
I am using |
Having the same issue, everything is working locally and when published to Azure I get the same error... |
I have the same issue when I run 2 or more queries one right after the other
|
I found a solution :) var config = { var connection = new sql.Connection(config); var request = new sql.Request(connection); |
I actually make a Is it preferred to have one global connection running in the bg? What about the conn's TTL? Any documentation regarding DB connection best practices? |
Related to #135 |
It seems working OK now, but I took a different approach: I used Promises for my queries I guess it is better at handling async requests and closing the connections properly |
Worked fine for me, better use a global connection rather connecting to the databse each time you have to make a request to avoid the 'Connection is closed Error'. |
I currently experience the same issue, having an node application running which connects to MS Azure, at some point the connection will be closed. I think I tried all of the possible solutions in this thread, however, I think that the connection losses ocure now less often, but still they happen. To start, I wonder what is the |
Hi, In the « config » variable, was the option « encrypt » as True ? options: {
|
Yes, without it I'm not even able to connect in the beginning |
@christopheranderson could you please post an example of both connection string and config object? |
Sorry @patriksimek - I deleted my post on GitHub (in case you see this in email). Copy+Paste error I did twice when adding app settings to the Web App, so nothing to do with mssql. Should have finished testing before posting. If you'd like some user experience feedback, this error message is the one that pops up if the connection closes on purpose or due to error. I kept assuming it was a bug because my request spit out this error and the connection wasn't emitting an
|
I seem to be having the same issue, everything works fine when connecting locally but if I try to reach my azure db it throws the 'Connection is Closed' error.
Definitely not a login permission issue as I tried it with my primary dev account and had the same issue. I think I read that in a recent version we can use our standard connection string now? Maybe I'll give that a try and see if there is any difference. EDIT : Nevermind, my issue was just related to using the full connection string they provide me when MSSQL wants just the needed *****.database.windows.net |
I recently started getting
Should I rather do like this?
|
I'm also getting same error. Any suggestions about below code?
|
I am also getting indeterminate Connection is Closed errors in my AWS Lambdas. function insertAliasTable(entityId, tenant, aliases, connection) {
const aliasTable = constructAliasSqlTable();
aliases.forEach((alias) => {
aliasTable.rows.add(tenant, entityId, alias.AliasKey, alias.Value);
});
return new sql.Request(connection).bulk(aliasTable);
}
function insertMetadataTable(entityId, tenant, metadataKey, metadataValue, connection) {
const request = constructMetadataRequest(entityId, tenant, metadataKey, metadataValue,
connection);
return request.query(
'INSERT INTO Metadata VALUES(@tenant, @entityId, @metadataKey, @metadataValue)');
}
function updateMetadataTable(entityId, tenant, metadataKey, metadataValue, connection) {
const request = constructMetadataRequest(entityId, tenant, metadataKey, metadataValue,
connection);
return request.query('UPDATE Metadata SET Value=@metadataValue WHERE Tenant=@tenant AND EntityId=@entityId' +
' AND MetaKey=@metadataKey');
}
function verifyExistence(rows, type) {
if (!rows.length) {
throw new Error(`${type} Not Found`);
}
return rows[0].EntityId;
}
function connectToDatabase(execute) {
return new Promise((resolve, reject) => {
loadConfig()
.then(sql.connect)
.then(execute)
.then(resolve)
.catch(error => reject(sqlErrorParser(error)));
});
}
function verifyId(id, tenant, connection) {
return selectEntityIdFromEntity(id, tenant, connection)
.then(rows => (verifyExistence(rows, 'Entity')));
}
function connectAndVerifyId(entityId, tenant, execute) {
return connectToDatabase(
connection => (
verifyId(entityId, tenant, connection)
.then(execute)
)
);
}
function addAliasesById(entityId, tenant, aliases) {
return connectAndVerifyId(entityId, tenant,
connection => (
insertAliasTable(entityId, tenant, aliases, connection)
.then(() => (entityId))
)
);
}
function addMetadataById(entityId, tenant, metadataKey, metadataValue) {
return connectAndVerifyId(entityId, tenant,
connection => (
insertMetadataTable(entityId, tenant, metadataKey, metadataValue, connection)
)
);
}
function updateMetadataById(entityId, tenant, metadataKey, metadataValue) {
return connectAndVerifyId(entityId, tenant,
connection => (
updateMetadataTable(entityId, tenant, metadataKey, metadataValue, connection)
)
);
} |
I was able to solve the connection error problem in my lambdas by abstracting the connections away to module: 'use strict';
const sql = require('mssql');
const configLoader = require('./configLoader');
let connection = null;
function getDatabaseConnection() {
return new Promise((resolve, reject) => {
if (connection) {
resolve(connection);
} else {
configLoader.getDatabaseConfig()
.then((config) => {
connection = new sql.Connection(config);
connection.connect()
.then(() => {
resolve(connection);
},
(error) => {
log.error(`Connecting error: ${error}`);
reject(error);
});
})
.catch(reject);
}
});
} And refactoring the persistence functions: function connectToDatabase(execute) {
return new Promise((resolve, reject) => {
connectionManager.getDatabaseConnection()
.then(execute)
.then(resolve)
.catch(error => reject(sqlErrorParser(error)));
});
} |
I'm finding rampant "Connection is Closed" errors when attempting to paralellize multiple requests using the native For example if I try to run 4 queries in parallel to SELECT from the same table, the first Request in the If I try to run 4 I believe this bug is related to the Tedious Bug. |
@dw1284's solution fixed the "Connection is closed" error for me. Changed from: sql.connect(dbConfig).then(function () {
new sql.Request()
.execute(storedProcName).then(function (recordset) { Changed to: sql.connect(dbConfig).then(function (connection) {
new sql.Request(connection)
.execute(storedProcName).then(function (recordset) { |
Unfortunately I have tried every iteration of the above solutions to no avail. This isnt happening locally, only when I try to hit a service that is deployed to Heroku. @patriksimek any suggestions? A sample of code:
|
@takotuesday Am I correct in my understanding that you have a service deployed to Heroku that cannot access your database (you get a connection is closed error)? You also can access the database locally using the same code and credentials? My next step would be to verify you can ping the database from your service. There might be an internet configuration (e.g. firewall) gone wrong for your deployed service. |
I'm using webdriverIo and Javascript. WebdriverIo cannot handle promises. I am trying to connect to the database and I get name: 'ConnectionError', var connection = new sql.Connection(config); connection.connect(function(err){ var request = new sql.Request(connection); |
@sumalatha1920 |
I always got the error "sql.Connection is not a constructor", why? |
@occulter |
Note that mssql implements a pool manager, so acquiring a connection is really coming from the pool. So new connection and close on every request. Pool also handles bad connection ejecting |
I encountered the same problem. But I solved it by creating a delay before running the query. It seems like mssql needs at least 30 microseconds to check the mssql connection at the library declaration level before running the query. Otherwise, I always got the "Connection is closed" error. Below is my delay function: const mssqlconn=require('./api/models/mssql-connection'); var runQuery=function () { function delay(time) { delay(30).then(()=>{ |
This is an old issue and we've encountered similar recently using Azure SQL Database, and have fixed them. Upgrading to the latest version of Tedious / MSSQL should resolve this, but if it doesn't, feel free to reopen. |
Previously I would use const sql = require('mssql');
var dbConfig = {
user: 'Jason',
password: auth.DBPassword(),
server: 'yourserver.database.windows.net',
database: 'runetracker',
options: {
encrypt: true
}
}
// Create a global DB Connection
ConnectToDB();
function ConnectToDB() {
sql.connect(dbConfig, (err) => {
if (err) return console.log('Could not create DB Connection!');
console.log('Successfully Connected to Database!');
});
} |
@willmorgan can you please re open the issue ? |
@s-a I think this is old enough that it probably warrants a new issue if you think this still exists. Are you using SQL Express like the OP? If so, then it's not something wrong with the library. |
@dhensby thank you for the fast response, Yes I think they use Maybe for other coders helpfull. This is the way I just worked arround. // eslint-disable-next-line max-params
Provider.prototype.waitForDatabaseConnection = async function (db, msg, timeout, attempts, currentAttempt) {
if (!db.connection._connected || db.connection._connecting) {
await delay(timeout)
await this.connectDatabase(db, msg, attempts, currentAttempt + 1)
}
}
Provider.prototype.connectDatabase = async function (db, msg, attempts, currentAttempt) {
const ca = (currentAttempt || 0)
const delay = 100
if (((currentAttempt || 0) < attempts) && !db.connection._connected) { // FIXME: workaround for ms sql connection lost. Seems like server drops too many connections
this.log.warn(`Microsoft SQL Server connection problem.`)
this.log.warn(`Delay ${delay} milliseconds to give Microsoft SQL Server system a chance to cooldown...`)
this.log.warn(`Try to reconnect ${msg} (${ca + 1} of ${attempts} attempts)`)
await db.connect()
await this.waitForDatabaseConnection(db, msg, delay, attempts, ca)
}
} I call this |
There shouldn't really be any need for this logic you have. When a database connection is dropped that's just one connection in the whole pool. It seems like you're acting on one dropped connection and deciding to spin up an entirely new pool rather than just trying to get another connection from the existing pool. In terms of other issues about SQL Express, I would just suggest using GitHub search for issues in this repo (that's all I would do) and there have been some over the past few years that all had common issues around SQLExpress. |
Hi..
I'm able to access my MSSQL from MSQL Studio.
defined my index.js file as below, but it keeps give me this error:
{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED'}
what wrong I'm doing, if below is my code, thanks
});
The text was updated successfully, but these errors were encountered: