7

I am writing an application using NodeJS, Express, mysql, so far everything works fine, but when I run my application after sometime when mysql connection is interrupted my application throughs this exception and my application goes down.

Error: read ECONNRESET
    at errnoException (net.js:901:11)
    at TCP.onread (net.js:556:19)

From another stackquestion i came to know that i have to handle such uncaught exceptions like this.

process.on('uncaughtException', function(err) {
    console.log('Caught exception: ' + err);
    console.log(err.stack);
});

after this now my application does not exit, but instead it hangs up, so my question is how do I handle this exception so that mysql connection is ok even after this exception and my application does not hang up.

user2009750
  • 3,169
  • 5
  • 35
  • 58
  • Take a look at this [node-js-econnreset](http://stackoverflow.com/questions/17245881/node-js-econnreset) – Damodaran Mar 24 '14 at 06:46
  • yes i got this `process.on(...)` from the question you mentioned it only catches the exception but my application still hangs, probably because i'm loosing connection with mysql and i am just catching this exception, i want to re establish connection with mysql, i just don't know how should i do that – user2009750 Mar 24 '14 at 06:55
  • i tried re connecting with mysql, which gives error about an enqeued connection, then i tried connection.destroy before reconnecting but still gives that enqueued error. – user2009750 Mar 24 '14 at 06:56
  • 1
    I guess that once your mysql connection drops you need to try reconnecting after some time. During that time you should make sure that your application doesn't try to use mysql queries. I think that the hanging out is because some code that uses mysql. – Krasimir Mar 24 '14 at 06:57
  • @Krasimir yes my application uses mysql queries alot. – user2009750 Mar 24 '14 at 07:01
  • 1
    Then imagine what will happen if you send a lot of queries and there is nothing to process them. Probably you have a new exception thrown every time. I'll suggest to add a layer between your code and the mysql adapter. Just a proxy and if the connection is lost somehow delay the requests till your connection is recovered. – Krasimir Mar 24 '14 at 07:07
  • @Krasimir i think i am getting your idea, i think while the connection is lost i can show user an Internal Server Error untill i get the connection back but that would give a bad impression, isn't there a smooth solution to this. About your suggestion that i should delay these requests i don't know how much should i delay these requests. – user2009750 Mar 24 '14 at 07:15
  • We can't really reply on your question without knowing what your app is made for. If you can you may just return an empty response or some kind of message showing that there is a problem. – Krasimir Mar 24 '14 at 07:24
  • @Krasimir The MySQL connection-pool adapter for node should do all that itself. It's n a problem the application should have to deal with. – user207421 Apr 06 '14 at 23:52
  • @EJP I agree with you. However I'm not sure that the adapter is made like that. – Krasimir Apr 07 '14 at 11:08

2 Answers2

3

I'm not sure if you're using the node-mysql module for your project, but I was, and I encountered the same ECONNRESET issue. Here's a repeat of my answer on my issue:

I reached out to the node-mysql folks on their Github page and got some firm answers.

  1. MySQL does indeed prune idle connections. There's a MySQL variable "wait_timeout" that sets the number of second before timeout and the default is 8 hours. We can set the default to be much larger than that. Use show variables like 'wait_timeout'; to view your timeout setting and set wait_timeout=28800; to change it.

  2. According to this issue, node-mysql doesn't prune pool connections after these sorts of disconnections. The module developers recommended using a heartbeat to keep the connection alive such as calling SELECT 1; on an interval. They also recommended using the node-pool module and its idleTimeoutMillis option to automatically prune idle connections.

Community
  • 1
  • 1
Brent Traut
  • 5,614
  • 6
  • 29
  • 54
  • thanks but i solved this some how was a tricky thing took time but i found a workaround then – user2009750 Apr 14 '14 at 13:27
  • Did you solve it the same way? I'd love to hear your solution. – Brent Traut Apr 15 '14 at 00:15
  • Thanks to So community, First I caught all uncaught exceptions, which made my application not to exit abnormally, second the problem of hanging I had was because when server would close the connection, all my requested queries would fail and my server would simply hang up I guess its node-mysql bug, but I solved it using connecting pooling, in pooling if server close the connection its re-aquired after a second or so again so my problem was solved this way. I still think that somehow node architecture is not mature enough but lets see what happens next. – user2009750 Apr 15 '14 at 12:51
  • @krasimir comment will probably help why server hangs up when you loose connection. – user2009750 Apr 15 '14 at 12:55
2

I found the solution and I am posting if someone else is facing the same problem this might help you guys as well.

First I caught all uncaught exceptions, which made my application not to exit abnormally.

Second the problem of hanging I had was because when server would close the connection, all my requested queries would fail and my server would simply hang up I guess its node-mysql bug, but I solved it using connecting pooling, in pooling if server close the connection its re-aquired after a second or so again so my problem was solved this way.

Here is how to make most out of node-mysql pooling

user2009750
  • 3,169
  • 5
  • 35
  • 58