Other issue related to handshake inactivity

[Aminadav]

http://stackoverflow.com/questions/35553432/error-handshake-inactivity-timeout-in-node-js-mysql-module

I'm using node-mysql and most of the queries. Working. some queries not working.
I tried every version of Node (from 0.5...) until (5.6.0), I also tried (4.0) and (4.1), Nothing helps.

I tried to change maually, and didn't work. I tried to change the sequence file to: this._idleTimeout = -1; and didn't help.

I read the issues and GitHub, and nothing helped.

I can try to fix it by myself, but I need more information. Where is the timeout, why? when? what is this type of message? Where is the timeout came from?

MYSQL_ERROR     { [Error: Handshake inactivity timeout]  
code: 'PROTOCOL_SEQUENCE_TIMEOUT',   fatal: true,   
timeout: 10000 }  

I will bounty it for about 100-150 points, and reward the best answer, and I can do (need to wait 2 days).
But even if you answer before, you will get the prize.

[dougwilson]

Hi! Unfortunately I can't really give any good answer to your question because there is not enough information. I can say that error message occurs because after setting up the TCP connection to your MySQL server, the server took too long to complete the handshake.

Can you provide the code you are using that causes the error? MySQL server version? What debugging have you done so far? Can you take a look at the packets using Wireshark or similar to see if the communication is getting hung up somewhere?

[y2krelic]

I'm getting a similar error out of the blue! Node version v0.12.4. I did notice that my project pulls in a new version of "readable-stream" dependency for mysql. The error started to happen as soon as readable-stream: 1.1.14 was used. Does anyone know how to resolve this? @dougwilson

Error: Handshake inactivity timeout
[2016-04-21 11:38:25.645] [84403] [ERROR] ../util/connection - { code: 'PROTOCOL_SEQUENCE_TIMEOUT',
  fatal: true,
  timeout: 10000 }

Build time dependencies pulled in:

+-- mysql@2.10.2 
| +-- bignumber.js@2.1.4 
| `-- readable-stream@1.1.14

+---------------------------------------------------+----------+
| Variable_name                                     | Value    |
+---------------------------------------------------+----------+
| innodb_lock_wait_timeout                          | 50       |
| innodb_spin_wait_delay                            | 6        |
| lock_wait_timeout                                 | 31536000 |
| performance_schema_events_waits_history_long_size | 10000    |
| performance_schema_events_waits_history_size      | 10       |
| wait_timeout                                      | 3600     |
+---------------------------------------------------+----------+

MySQL Version:

+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.28                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.28                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

[happilymarrieddad]

If you look at the Sequence.js at line 24 you will see a passed in timeout. That value is what is used in Protocol.js to determine if there is a timeout error on line 159. I just set that value manually to 100000 and I haven't had that issue again.

[dougwilson]

Hi @y2krelic, I'm not sure this issue is related to readable-stream; the issue was opened on Feb 22, 2016, but readable-stream 1.1.14 was not released until April 13, 2016, so there is no way this issue would have been using that version of readable-stream. Can you provide the code you are using that causes the error? What debugging have you done so far? Can you take a look at the packets using Wireshark or similar to see if the communication is getting hung up somewhere?

[happilymarrieddad]

@dougwilson It's a timeout issue. If you follow Sequence.js you will see where it is. This was driving me crazy for a long time until I was able to figure it out. All that stuff people were saying about changing the _idleTimeout didn't work for me. I changed Node versions a bunch of times.. it's a bug in Sequence. If you change the this._timeout = options.timeout to this._timeout = 1000000 on line 24 you will never see that again. We have 9 clustered servers with a lot of users and I haven't seen that error since. I really hope this helps. Thanks!

[dougwilson]

Thanks @happilymarrieddad! That is interesting. Now, making that change is not going to work for releasing this module, because it would no longer allow people to set timeouts (and would not allow queries to take longer than 100 seconds), but perhaps you are on to something!

Can you provide all the following, please?

1. The version of Node.js you are using.
2. The version of this module you are using.
3. The MySQL server version you are connecting to.
4. The code you are using that causes the error?

Some people have provided pieces of this information about, but no one has provided the code that is being used so I can copy and paste something to run & reproduce. Without having a way to reproduce, it's practically impossible for me to fix anything until I just happen to stumble upon the bug myself, as yo fix it, I need to debug it to figure out what is going wrong. I hope this makes sense and you can provide all the information for me to get this going!

[happilymarrieddad]

@dougwilson

What timeout is being set in Sequence timeouts? Do I set it with the pool config and if so which one is it. I'd rather pass it in then hardcode it.

1. 5.11

"version": "2.10.2"

3. 5.5
4. It happens intermittently all over our code so I'm just posting one of the functions where it happens.

function(cb) {
                    pool.query('SELECT * FROM order_comments WHERE order_id = ?',[data.order_id],function(err,rows) {
                        if (err) {
                            console.log(err);
                            results.success = 0;
                            results.message = 'Failed to get order comments for duplication.'
                            console.log(results.message);
                            finish();
                        } else {
                            storage.order_comments = rows;
                            if (rows.length){
                                var p = rows[0];
                                p.id = 0;
                                delete p.timestamp;
                                p.text = 'Order duplicated by ' + data.username;
                                storage.order_comments.push(p);
                            }
                            cb();
                        }
                    });
                },

[happilymarrieddad]

@dougwilson oh btw I'm clustering MySQL and clustering horizontally with my app on separate servers with loadbalancers across so I need a longer timeout.

[dougwilson]

Hi @happilymarrieddad, I did not know this issue was about using the pool, my bad! The handshake inactivity timeout is not a bug. You can see all the pool configuration options at https://github.com/felixge/node-mysql/blob/master/Readme.md#pool-options in particular, look to the "acquireTimeout" to adjust this timeout. By default it is 10 seconds, and your code change means you are setting all timeouts to 100 seconds. If you undo that code change and just set "acquireTimeout" to 100000, does that resolve your issue? If so, then I don't see a bug here, only your MySQL servers periodically going very slow (taking longer than 10 seconds to log in!).

[happilymarrieddad]

@dougwilson I set all the timeouts to 1000000 and was still getting the error. The only time it worked was when I hard coded the value in Sequence... Not sure why???

[dougwilson]

Hi @happilymarrieddad, hm. When you remove your hard-coding, can you debug the flow of the timeouts through the code? There isn't much I can guess unless I can debug your code, but I don't have your code to do it :) So far your issue seems like a timeout issue, since when you hard code this module to ignore any set timeouts and always just use 100 seconds it works, so the question is now why when you use the settings it is not working. I'm not sure how I can proceed with helping you more at this point unless you can share your full, complete code as that is not working, complete with instructions for hoe I can run it, etc. Thoughts?

[dougwilson]

Hi @happilymarrieddad, I'm going to close this issue since I haven't heard back in 24 days now.