Closed connections stuck in CLOSE_WAIT

[coopernurse]

This may be related to #43 - the symptom in my app was that I ran out of file descriptors. lsof revealed that I was leaking mysql connections.

I have traced the origins of this issue to changes made between the npm 0.6.0 and 0.7.0 releases. 0.7.0 and 0.8.0 appear to have this issue.

Here's some examples (my app code is unchanged across these runs):

james@ubuntu:~/nook$ npm install mysql@0.8.0
james@ubuntu:~/nook$ node app.js &
(access webapp in browser)
james@ubuntu:~/nook$ sudo lsof -p 4981 -P -n | grep 3306
node    4981 james    8u  IPv4 825503      0t0     TCP 127.0.0.1:53629->127.0.0.1:3306 (CLOSE_WAIT)
(make another request in browser)
james@ubuntu:~/nook$ sudo lsof -p 4981 -P -n | grep 3306
node    4981 james    8u  IPv4 825503      0t0     TCP 127.0.0.1:53629->127.0.0.1:3306 (CLOSE_WAIT)
node    4981 james    9u  IPv4 825662      0t0     TCP 127.0.0.1:53630->127.0.0.1:3306 (CLOSE_WAIT)

Downgrade to 0.6.0:

james@ubuntu:~/nook$ npm install mysql@0.6.0
james@ubuntu:~/nook$ node app.js &
(access webapp in browser)
james@ubuntu:~/nook$ sudo lsof -p 5013 -P -n | grep 3306
(no output - as expected)
(access webapp 10x times in browser)
james@ubuntu:~/nook$ sudo lsof -p 5013 -P -n | grep 3306
(no output - as expected)

I will see if I can figure out what changed between releases. My guess is the conn.end() behavior has changed. From the documentation it sounds like end() attempts to gracefully close the connection. Perhaps another packet needs to be sent after the COM_QUIT?

thank you

-- James

[coopernurse]

Another data point. It appears if I call destroy() in a callback from end() that this issue goes away.

client.end(function() { client.destroy(); });

But I'm not sure why this would be necessary. There's some discussion on stackoverflow about mysqld settings that could affect this. I'm using default values for wait_timeout and interactive_timeout (28800 seconds, or 8 minutes). My suspicion is that CLOSE_WAIT lingers for 8 minutes and then mysqld hangs up.

Not sure if that helps at all.

[felixge]

Can you try to add: self.destroy() above this line?

https://github.com/felixge/node-mysql/blob/master/lib/mysql/client.js#L66

[coopernurse]

Hi Felix,

Yes, that seems to work. Thanks. You want to make that change, or should I issues a pull request?

Thanks!

-- James

[felixge]

Well, unfortunately this causes another test to fail:

node test/system/test-client-reconnect.js

/Users/felix/.node_libraries/.npm/gently/0.8.0/package/lib/gently/gently.js:112
  throw new Error
        ^
Error: Expected call to connectCb2() did not happen (process exit)
    at Gently.verify (/Users/felix/.node_libraries/.npm/gently/0.8.0/package/lib/gently/gently.js:112:9)
    at EventEmitter.<anonymous> (/Users/felix/.node_libraries/.npm/gently/0.8.0/package/lib/gently/gently.js:9:10)
    at EventEmitter.emit (events.js:48:20)

I won't have time to look into this right away, so if you have some time to investigate - that'd be great : ).

[coopernurse]

hey there,

Ok, I'll try to take a closer look in a day or two!

-- James

[felixge]

This should be fixed in https://github.com/felixge/node-mysql/tree/refactoring (to be released as 0.9.2 soon).

The new version no longer has a connect method, and instead automatically connects as needed.

--fg