Intermittent lost connection to Azure SQL

[tysjiang]

I've been getting this error intermittently with tedious:

Fri Aug 14 2015 05:29:16 GMT+0000 (Coordinated Universal Time): Unaught exception: ConnectionError: Connection lost - read ECONNRESET at Connection.socketError (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:797:26) at Socket. (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:33:15) at Socket.emit (events.js:117:20) at net.js:441:14 at process._tickCallback (node.js:442:13)

The error stalls the entire node server and renders it unresponsive for around 10-15 minutes. Afterwards, the server recovers by itself automatically.

I'm on the latest tedious 1.12.2 and using it with the tedious-connection-pool module 0.3.8 (although the issue is present prior to tedious 1.12.2 and has been happening in all the versions for the past few months)

What is interesting is that it seems like the issue happens much more frequently these days than before.

I'm not sure if the above error message is helpful or not but if it's not, please inform me what other debug or log information I can get you good folks. I've been testing out tedious and besides this particular showstopper issue for me, everything looks great.

As for servers, I'm using node 32bit v0.10.36 (although I've tested this with the latest node 12 and the issue is present there too) connecting to Azure SQL (v12 DB)

[palakautomation]

same here.

Application has thrown an uncaught exception and is terminated:
ConnectionError: Connection lost - read ECONNRESET
    at Connection.socketError (D:\home\site\wwwroot\node_modules\mssql\node_modules\tedious\lib\connection.js:797:26)
    at Socket.<anonymous> (D:\home\site\wwwroot\node_modules\mssql\node_modules\tedious\lib\connection.js:33:15)
    at Socket.emit (events.js:95:17)
    at net.js:440:14
    at process._tickCallback (node.js:419:13)

[arthurschreiber]

I'm not using Azure SQL and thus can not really help much on this topic without a lot more information on this. You could enable network logging and write all data out to a file so I could have a look at the data that is sent over by the server.

[Reubend]

I'm having this same issue. The exact data returned is

{ [ConnectionError: Connection is closed.]
      name: 'ConnectionError',
      message: 'Connection is closed.',
      code: 'ECONNCLOSED' }

I'm using Tedious through the node-mssql module, and so I normally have several Tedious connections open at the same time. I can try to get more info if you'd like, although I don't really know what I'm doing with this type of stuff. I'm not sure how to enable network logging; do you mean from the SQL Server?

[dmiddlecamp]

I seem to be hitting this while testing a nodejs app on "Azure Web Apps"

[ConnectionError: Connection lost - read ECONNRESET]
  name: 'ConnectionError',
  message: 'Connection lost - read ECONNRESET',
  code: 'ESOCKET' } ConnectionError: Connection lost - read ECONNRESET
    at Connection.socketError (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:795:26)
    at Socket.<anonymous> (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:33:15)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:169:7)
    at emitErrorNT (net.js:1250:8)
    at doNTCallback2 (node.js:429:9)
    at process._tickCallback (node.js:343:17)

I'm using tedious version ~1.12.3, and mssql version 2.3.1

(doesn't matter what bit / version of node I use, I've tried a ton)

edit: I thought this was preventing me from using the database, turns out I was doing something silly, and I accidentally removed the options: { encrypt: true } setting!

[Reubend]

Yeah, after updating, I'm still experiencing this issue. My setup is almost exactly the same, with an Azure SQL DB connected to an Azure Web App.

I seem to be hitting this while testing a nodejs app on "Azure Web Apps"

|[ConnectionError: Connection lost - read ECONNRESET] name: 'ConnectionError', message: 'Connection lost - read ECONNRESET', code: 'ESOCKET' } ConnectionError: Connection lost - read ECONNRESET at Connection.socketError (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:795:26) at Socket. (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:33:15) at emitOne (events.js:77:13) at Socket.emit (events.js:169:7) at emitErrorNT (net.js:1250:8) at doNTCallback2 (node.js:429:9) at process._tickCallback (node.js:343:17) |

I'm using tedious version ~1.12.3, and mssql version 2.3.1

— Reply to this email directly or view it on GitHub https://github.com/pekim/tedious/issues/300#issuecomment-142976236.

[lawrips]

Here's info I've learned from running in production - sharing here in the hope it's helpful.

There's a load balancer in Azure that has a default timeout of 4mins and will shut off idle connections. For other socket based services (e.g. mongo, imap, etc), I've had to set the keep-alive ping to be shorter than the 4 min mark (its ideal when the library supports this in the connection options as with mongo). So if this is the issue, one option is to implement that in tedious.

Another option worth investigating is this - https://azure.microsoft.com/en-us/blog/new-configurable-idle-timeout-for-azure-load-balancer/ although have not tried this myself (and not sure if it's related to sql).

Another option is to manage retries and reconnects in code. Here's a good article explaining some of the differences between azure sql vs sql and a recommended approach in c# which could be borrowed from:

http://peterkellner.net/2011/01/21/sqlazure-connection-retry-problem-using-best-practices/

[arthurschreiber]

@lawrips That's quite interesting! Adding a keep-alive ping to tedious would be quite simple, as node.js exposes this on all sockets. I'll see if we can add this as on option for the next version of tedious.

[lawrips]

great ! love tedious - think this will be a nice addition

[christopheranderson]

:+1: Let me know if you need any help with this @arthurschreiber. I've definitely seen some issues with this.

[ashelley]

Hello,

I'm also dealing with this issue. In order to facilitate retry logic when a socket error occurs I needed to add a patch to tedious to ensure that a callback gets called when a request is in flight.

I'm still working on testing this patch but without it, the request callback is never called and your application has to implement its own state on the connection to ensure that your request callback gets called when a socket error occurs.

Note that this patch does not yet work with tedious-connection-pool because the connection pool closes the connection (changes the drivers state) before the driver can route to the proper state to call the request's callback. The patch can be reviewed here:

https://github.com/pekim/tedious/issues/324

[arthurschreiber]

@lawrips I just checked the tedious code, and we already enable socket keepAlive, the same way e.g. mongodb drivers enable it. So a missing keepAlive can not be the cause of your lost connection issues.

[Reubend]

This is still occurring for me. I'm going to see if I can avoid the error by using a connection pool that swaps the connection every 30 seconds.

[jtmilne]

Please report back if that helps. I'm also having the same issue.

[Reubend]

Unfortunately, that didn't help. Even with frequent swapping of the connections, I still got the error. This leads me to believe that it has nothing to do with a timeout.

[lindo-jmm]

We're having a pretty hard time with this as well.

[christopheranderson]

Just a PSA, I get this message when I screw up something in my config/connection string. Not saying that's what happening, but thought it was worth mentioning.

[lindo-jmm]

Is it intermittent or do you get it immediately?

From: Christopher Anderson Reply-To: pekim/tedious Date: Tuesday, January 19, 2016 at 2:35 PM To: pekim/tedious Cc: Sean Lindo Subject: Re: [tedious] Intermittent lost connection to Azure SQL (#300)

Just a PSA, I get this message when I screw up something in my config/connection string. Not saying that's what happening, but thought it was worth mentioning.

— Reply to this email directly or view it on GitHubhttps://github.com/pekim/tedious/issues/300#issuecomment-172961338.

[christopheranderson]

Immediately, since it was broken. But lots of people searching for the error message find this issue.

[Reubend]

I don't think that's what's going on for me because the connection initially works and I can make requests through it for a while. This error only appears after some time has passed (in my case).

[christopheranderson]

If you restart the app, does it work again? My first guess would be your running out of available connections to the SQL DB.

[ashelley]

@Reubend I think you already mentioned that you tried this but just wanted to reiterate the fact that you might need to ensure idle timeout on the connection pool is set to less than 4 minutes:

https://github.com/pekim/tedious-connection-pool#new-connectionpoolpoolconfig-connectionconfig

[Reubend]

Yes, if I restart the app, it works fine. I have the idle timeout set to only 30 seconds.

[ashelley]

I haven't yet implemented this myself in node but i wonder if you are hitting azure sql transient errors:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-csharp-retry-windows/

[Reubend]

I haven't written any code to deal with transient errors. That could definitely be the issue. However, I think it would be weird if my test DB has that many transient errors since I'm the only user sending requests.

[ashelley]

Transient errors on azure are typically "random" and not correlated to the number of requests. I've seen them happen as low as one time per day but sometimes they can occur more frequently. If your app still gets these errors if you don't have any connection pooling and create a new database connection on each request I would look at the transient error problem. In other words it might be worth trying to reproduce your problem without reusing any sockets.

[Reubend]

Interesting. I'll write up some code later tonight to see if it's indeed a transient error issue.

[sashasochka]

This is the first time I'm trying to connect node.js to mssql on Azure App Service and haven't had success. I'm always getting ConnectionError: Connection lost - read ECONNRESET at ConnectionError (D:\home\site\wwwroot\node_modules\tedious\lib\errors.js:21:12) at Connection.socketError (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:575:56) at emitOne (events.js:77:13) at Socket.emit (events.js:169:7) at emitErrorNT (net.js:1256:8) at nextTickCallbackWith2Args (node.js:478:9) at process._tickDomainCallback (node.js:433:17) at process.<anonymous> (D:\home\site\wwwroot\node_modules\async-listener\index.js:19:15)

For me this is a persistent issue, it never worked even for a minute. I'm sure there is no problem with configs or firewalls. I'm using loopback.js with it's loopback-connector-mssql module.

[lindo-jmm]

Out of curiosity, do you have the encrypt option set to true?

Sent from Outlook Mobilehttps://aka.ms/qtex0l

On Tue, Jan 19, 2016 at 4:31 PM -0800, "Oleksandr Sochka" <[email protected]mailto:[email protected]> wrote:

This is the first time I'm trying to connect node.js to mssql on Azure App Service and haven't had success. I'm always getting ConnectionError: Connection lost - read ECONNRESET at ConnectionError (D:\home\site\wwwroot\node_modules\tedious\lib\errors.js:21:12) at Connection.socketError (D:\home\site\wwwroot\node_modules\tedious\lib\connection.js:575:56) at emitOne (events.js:77:13) at Socket.emit (events.js:169:7) at emitErrorNT (net.js:1256:8) at nextTickCallbackWith2Args (node.js:478:9) at process._tickDomainCallback (node.js:433:17) at process. (D:\home\site\wwwroot\node_modules\async-listener\index.js:19:15)

For me this is a persistent issue, it never worked even for a minute. I'm sure there is no problem with configs or firewalls. I'm using loopback.js with it's loopback-connector-mssql module.

Reply to this email directly or view it on GitHubhttps://github.com/pekim/tedious/issues/300#issuecomment-173033421.

[sashasochka]

Yeah, after writing my comment I went through the chain of libs and found out the comment in node-mssql which says you need to set enrypt to true. Which is not possible to do with loopback.js and therfore was not a part of their documentation. I will try a customly patched version now, will report here if that helps.

[jtmilne]

I get this error a couple times a day on all my node instances. I wrote a query wrapper to automatically retry on transient errors and it seems to work fine. Although it still is unsettling to see this happening.