41

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 }
Community
  • 1
  • 1
Aminadav Glickshtein
  • 23,232
  • 12
  • 77
  • 117
  • 3
    I think you are hitting a query multiple times instead of using single query because the execution time depends on number of queries fired. If you fire single query then execution will be fast and this error will not come. – Kunal Aug 03 '18 at 13:46
  • 1
    For anyone else finding this question, this error also shows if you try to create a connection/pool with invalid login credentials (or if you pass a port number in that doesn't match your mysql port, which is typically 3306). – AtAFork Apr 20 '20 at 22:22

7 Answers7

52

Ok, the timeout comes from the Protocol.js file line:162. If you checkout node-mysql you'll see that it is a variable "timeout" for queries. If you set the timeout to something a lot higher than 10000, the default, then the error should go away. An example is

pool = require('mysql').createPool({
    connectionLimit : 1000,
    connectTimeout  : 60 * 60 * 1000,
    acquireTimeout  : 60 * 60 * 1000,
    timeout         : 60 * 60 * 1000,
    host            : process.env.DB_HOST,
    user            : process.env.DB_USERNAME,
    password        : process.env.DB_PASSWORD,
    database        : process.env.DB_DATABASE
});

You can also edit the timeout in the Sequence.js file (node_modules/mysql/lib/protocol/sequences/Sequence.js)

this._timeout  = 100000;
Remi Guan
  • 21,506
  • 17
  • 64
  • 87
Nick Kotenberg
  • 914
  • 9
  • 8
  • 4
    "acquireTimeout" spelling mistake in the above code sample. – Sibaprasad Maiti Aug 30 '18 at 08:46
  • 1
    some time it happens due to invalid credential – Arjun Singh Apr 10 '19 at 05:15
  • I needed to do this in our system because it's incredibly busy sometimes and the delays can be up to 30 seconds. We have massive queries being run and sometimes they hit the servers at the same time. I've been scaling the mysql instance but there's still a state issue between the base instance and the replicas. – Nick Kotenberg Apr 26 '19 at 22:58
  • In sequence.js file I changed the time limit but did not work .can you please mention that where in protocol.js file we have to mentioned this credentials –  Jul 31 '19 at 16:07
20

For those deploying on AWS and experiencing this error, you'll need to make a change to the security group of your database/cluster and add an inbound rule where the source is the security group of your instance/s.

The inbound rule should look as follows:

Type: MySQL/Aurora
Protocol: TCP (default)
Port: 3306 (default)
Source: <security group of instance>
Description: <optional>
M3talM0nk3y
  • 1,382
  • 14
  • 22
  • 1
    i had to make new EC linux 2 platform as my old was depricated. This error made me scratch my head for couple of hours until i found your answer. – mArtinko5MB Feb 03 '22 at 12:33
6

If you are using Amazon's services, I was able to resolve this by changing the allowed IP Addresses in the security settings or by changing the open connections ports.

Berkay Torun
  • 91
  • 1
  • 1
  • Hi Kevin. You mean the subnet rules right? Cause I have allowed IP from my EC2 but still received this error. Its quite infrequent. – Lajpat Sep 11 '19 at 09:34
3

.end() is non-blocking, but this may lead your code to the same issue that was in mine - I was just calling .end() w/o waiting the operation to actually complete.

To actually wait for the connection end you can't just await dbConn.end(), because .end() doesn't return a promise. What you need is to create a promise and return it. Like the following:

From

connection.end();

To this 

connection.end(error => error ? reject(error) : resolve());

And for pool using 

connection.release();

To this

connection.release(error => error ? reject(error) : resolve());
Elminson De Oleo Baez
  • 580
  • 5
  • 13
  • 2
    I do believe your analysis is correct. I was getting the inactivity timeout when testing hitting the db in rapid fire. The actual time was short around 150 ms. How are you creating a Promise? Could you explain your answer for end()? – KingAndrew Sep 12 '20 at 00:10
1

for those deploying on aws and heroku! enter in rds db instance settigns and change the inboud rule --> source: any..

Heroku dont provide a ip specific, remember!

Edgar Olivar
  • 1,348
  • 13
  • 13
  • Allowing any source is fairly high risk, when it comes to running a database. – ThomasRedstone Oct 27 '20 at 12:51
  • I just did a little extra reading around this, and basically this *is* part of how you do it, but to do it securely there are a few extra steps, explained here: https://stackoverflow.com/a/42558720/340141 – ThomasRedstone Nov 04 '20 at 14:58
  • @ThomasRedstone thanks a lot, maybe that is the correct way to connect heroku with RDS with extra steps – Edgar Olivar Dec 07 '20 at 11:38
0

for me it was to correct the 'select' statment to select where checking the ID column (the primary key) and not column that it's type varchar , for example : not work

select from users where userName = 'aa';

work

select from users where userID = 1;
Feiga Lubow
  • 194
  • 3
  • 12
0

For those connecting to Amazon RDS and EC2, change the inbound rules for both RDS AND EC2 instance. i.e RDS should allow new IPv4 and EC2 should allow MySQL/Aurora.

Sushan
  • 67
  • 8