search

tediousjs / node-mssql

Microsoft SQL Server client for Node.js
https://tediousjs.github.io/node-mssql
MIT License
2.23k stars 465 forks source link

Sleeping session in MSSQL will not be cleared in 30 seconds in Azure Environment #1489

Closed izanami00 closed 1 year ago

izanami00 commented 1 year ago

I'm building an app which running in docker, and using typeorm/sequelize to connect mssql, it works fine in local environment, but does not work well in Azure Environment. The SQL Server is an on-premised server and can be accessed through Azure Hybrid Connection (built with Azure Service Bus).

Expected behaviour:

The sleeping session should be closed in 30 seconds.

Actual behaviour:

The sleeping session always present, but it will be cleared if the app is restarted. When the app is running in local environment and accessing the SQL Server with VPN, the connection will be cleared after 30 seconds.

Software versions

dhensby commented 1 year ago

Why do you expect the connection to be closed after 30 seconds?

Do you have any relevant configuration you can share?

izanami00 commented 1 year ago

@dhensby The default value idleTimeoutMillis of typeorm is 30000, it passed the value to mssql, it worked in local env, but did not work in Azure env when the app is hosted in docker and access db through Azure hybrid connection.

dhensby commented 1 year ago

Are the settings the same in the prod env as in local? I can't see why there'd be a reason in the library that this behaviour changes for you locally compared to your prod environment.

Have you turned on debug output and had a look at if there's any useful information?

izanami00 commented 1 year ago

In local env, my app created 1 pool and reused only 1 connection if only 1 API was being continuously accessed. But in production env, the number of sleeping sessions continues to increase, as the number of accessing increases. Typically, if one client continuously accessed the API in a short time, the number of sessions will not increase, but after a while, like 5 minutes, if I accessed the API again with same IP, a new session will be created. Meanwhile, if another client accessed the API, a new session will be created definitely.

dhensby commented 1 year ago

I'm not sure what help I can be here. You say it works as expected locally, therefore there seems to be a problem with your production setup and nothing wrong with the library.

If you have a bug to report then I'll need more information about it, how to reproduce, etc.

GitHub issues are not for general support but for bug reports, so unless you can provide me details of how to reproduce the problem, then there's not much help that I can provide.