Repository navigation
Pool connection doesn't reconnect well. #821
Description
I use amazon cloud server, with rds multi-AZ.
I'm using pooled connection, but it doesn't reconnect as expected.
I restarted RDS machine, and it started fail-over to other multi-AZ.
But at that point, connection was lost and not recovered well.
I'm just using pool.query() which don't have connection release leak. It must work as expected isn't it?
Activity
dougwilson commentedon May 18, 2014
I'm not really sure what you are asking. I also don't know anything about how Amazon's services work, so all that information about Amazon-specific terms is over my head. an you give me a more detailed description about what is not working, exactly? Also, are you getting any error from the pool?
miningpoolhub commentedon May 18, 2014
Oh, the connection count graph increased at certain point is not done automatically. The connection count didn't increase but increased after I restarted node process.
Amazon EC2 (just virtual machine computer) connects to RDS which is DB server.
Two RDS works, one is primary (master) and the other is slave. Each other changes when fail-over happens.
EC2 doesn't connect to RDS by IP, but it connects with dns name. dns endpoint is provided by Amazon and it redirects to primary RDS server at that time.
This RDS fail-over works well with other program written in python, reconnects very smoothly when RDS reboots or some problem occurs.
But node-mysql doesn't reconnect as expected.
Sorry that I couldn't get any info from pool. I didn't log about node-mysql that much.
But it's reproducible to this kind of problem.
I used cluster to run many nodejs process and each process has 10 connections max.
Maybe it's dns resolve problem, which previous pool connection trying to connect to same IP again and again. I'm not sure but just guessing because RDS works different, needs DNS info refreshed.
Curious thing is that it worked a bit too. I'm not sure it worked because it reconnected or some idle connection was established but some data was sent correctly.
Any idea?
dougwilson commentedon May 18, 2014
I don't really have any. So for the host -> DNS, that is just using the internal node.js core mechanisms, so I don't have any comment there about how that is working. Also, if the connection gets lost in this pool, a new connection will only be reestablished the next time to try to make a query, so if you are not making queries, the connections won't come back automatically.
Also, what version of this module are you using. Can you try the newest 2.3.0 if you are not using it and see if anything changes (there are various improvements/fixes that may be impacting you)?
miningpoolhub commentedon May 18, 2014
Queries are inserted frequently like 10~50 per second. Maybe I had to wait more time to see if things were going well, but I couldn't wait long because it is real production service.
I'm using 2.2.0
I will try new version.
dougwilson commentedon May 18, 2014
OK, 2.2.0 should be the newest I really wanted you to try, but you are using it.
I don't have access to any Amazon services to try and reproduce the issue to determine what is happening. It would be really helpful if you could figure out what the issue is so I would know how to make a patch for it, otherwise I'm just in the dark about what the issue you are experiencing is.
dougwilson commentedon May 18, 2014
/cc @sidorares
miningpoolhub commentedon May 19, 2014
What information do you need?
How can I enable debug info or things with node-mysql especially for pooled query?
I just use
pool = mysql.createPool()
pool.query()
Sorry that I'm new to nodejs, not sure how to examine it.
sidorares commentedon May 19, 2014
It's not very clear to me as well what problem are you solving. Number of connections in AWS console is expected to drop after restart - pool opens new connections only if there is no idle connection available and you requesting new one.
Are you actually getting errors on mysql client side because it's using old IP after failover? If not, could you add some connection identifying SQL and log results ( server id / thread id )?
miningpoolhub commentedon May 20, 2014
I will test more and write it soon. Thanks all.
miningpoolhub commentedon Jun 9, 2014
I analyzed deep into it today.
When Amazon tries Multi-AZ fail-over, two DB instance changes their position by redirecting endpoint url.
Amazon provides endpoint address like "testtesttest.cqofkxwqldcj.us-east-1.rds.amazonaws.com" not an IP and it redirects to master DB instance at that moment.
So connection IP may change time to time when fail-over occurs.
I couldn't get any error when doing fail-over with node-mysql. It works as it didn't disconnected at all.
DB records inserted successfully (not showing any error), but DB record data is not shown on DB. I think these newly inserted data have gone away to Backup DB instance.
Pool connection works perfectly when I tried just restarting DB instance not using Multi-AZ fail over. It says
PROTOCOL_CONNECTION_LOST,ECONNREFUSEDproperly and works well when DB instance restarted some minutes later.One more thing, I connected HeidiSQL to Master DB from my client PC and it disconnected immediately when I fail-over the DB instance. So HeidiSQL detects disconnection at right moment and tried to reconnect to endpoint url again.
I think node-mysql doesn't detect disconnection well or Amazon doesn't disconnect backup DB connection from which is from their server farm. Since other python based mysql projects works well with Amazon MySQL Multi-AZ, it maybe a specific issue with node-mysql only.
I'm using latest node-mysql version. I used today's latest souce.
dougwilson commentedon Jun 9, 2014
Yea, it does sound like there must be some disconnection indicator if other libs are seeing the disconnect. Just as a curiosity, would it be possible to try the
mysql2library to see if it has the same problem?miningpoolhub commentedon Jun 9, 2014
I tried mysql2 too but no luck.
I found that setting "queueLimit: 10" makes the pool query to output error. (It didn't even show error before) Since queries were stacked on queue unlimitedly, it didn't return error code. I thought it was inserted to DB correctly but it didn't at all.
When error occurs, I tried to reconnect something like this code.
pool.query(querystring, ..., ...,
function(err, result) {
if (err) {
pool.end();
pool = mysql.createPool(......);
}
It worked as expected but had some timing issue that createPool() was called constantly by other query's fail.
I increased queueLimit to 100 and the error did not happen frequently but happened time to time.
I want to know for clean and robust code to reset pool connection and reinitialization.
One thing the main problem is that Amazon Multi-AZ doesn't disconnect previous connection, pool also doesn't get disconnect event too.
node-mysql catches disconnection event when Amazon RDS just reboots, so this is some bug about Multi-AZ fail over on Amazon side.
Any idea for workaround or clean code?
dougwilson commentedon Jun 9, 2014
Can you see what the behavior is if you set your
connectTimeoutvalue low? For example, add " connectTimeout: 10" to your configuration.19 remaining items
dougwilson commentedon Jul 6, 2014
If you like, please try out the patch with
npm install felixge/node-mysql#945b92eand let me know if it corrects your issue.mseddon commentedon Jul 7, 2014
Printing the result of doing SELECT CURRENT_TIMESTAMP every 2 seconds and then triggering a failover still doesn't cause any reconnect after ten seconds, or indeed five minutes.
Same problem as before when the RST packet is not received.
@miningpoolhub does this patch work for you?
dougwilson commentedon Jul 7, 2014
@mseddon can you paste me the code you are using to test? It is important to note that the patch only affects
pool.getConnectionright now.mseddon commentedon Jul 7, 2014
ah, that may be it, one moment while I try again using that. Cheers.
mseddon commentedon Jul 7, 2014
Hi- switching the test to use pool.getConnection seems to work better, I'll have a play but this might fix it after all, sorry about that. Thanks :)
dougwilson commentedon Jul 7, 2014
@mseddon no problem :) I know there is still work to be done--but from now, it's actually down deep into Node.js internals. When you send data over TCP, you get ACKs for each packet; we should be able to timeout if when sending off the sequence doesn't get ACKs back, but that is not straight-forward in Node.js since the details have been extracted away from us.
mseddon commentedon Jul 7, 2014
So, one observation (though probably not surprising)- if I set the connectionLimit to 1 to simulate a full connection pool, and failover within a query, reconnects still don't happen. I would imagine that this is because we're hung waiting for a free connection, and your timeout check occurs once we have a free connection?
dougwilson commentedon Jul 7, 2014
Correct. The query is hung because of the severed connection. You can always do
conn.query({sql: 'SELECT 1', timeout: 30000})to give the query a 30s timeout, but that's not very practical. AFAIK, Node.js abstracts too much away to know that the TCP connection is gone in the middle of the query without aFINorRSTpacket to tell us.mseddon commentedon Jul 7, 2014
Hmm, does timeout start when the conn.query() command is issued, or when the request is finally submitted to mysql? It's not pretty but it could work for my case.
dougwilson commentedon Jul 7, 2014
The timeout is actually based on sequence packet activity, so it would start when the query packet is sent to MySQL server, rather than at the time you called
.query().mseddon commentedon Jul 7, 2014
Ok, thanks, that should be good enough!
dougwilson commentedon Jul 7, 2014
Cool :) I'm still going to be working on adding more protections, though. It may be interesting to know that every command now accepts a timeout option, so you could ping with a timeout
conn.ping({timeout: 1000}), etc.Add acquireTimeout pool option