Node-mysql2: Error: Can't add new command when connection is in closed state

[Rakeshvishnoi029]

Hi

I fascing last 2 days this error please help me...

{ Error: read ETIMEDOUT
at TCP.onread (net.js:622:25)
errno: 'ETIMEDOUT',
code: 'ETIMEDOUT',
syscall: 'read',
fatal: true }
{ Error: Can't add new command when connection is in closed state
at PoolConnection._addCommandClosedState

I use mysql 2 and connect Pool

var mysql = require('mysql2');
var mysqlPool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'xyz',
database: 'xyz',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});

module.exports = mysqlPool;

55|| { Error: read ETIMEDOUT
55|| at TCP.onread (net.js:622:25)
55|| errno: 'ETIMEDOUT',
55|| code: 'ETIMEDOUT',
55| | syscall: 'read',
55|| fatal: true }
55|| { Error: Can't add new command when connection is in closed state
55| at PoolConnection._addCommandClosedState

[digiperfect-janak]

+1 we have been getting this issue since yesterday.

Error: This socket has been ended by the other party

followed by

Error: Can't add new command when connection is in closed state

[yunfan]

we too :[

[zebullax]

Same issue here, the only hack is to stop and restart the node app...

[Akash0333]

Same issue here, please tell me how we can resolve it

[ovidiu-balau]

Anyone found a fix for this?

[nag944]

Any news on this problem ? After connection timed out, it cannot be restored at all - as all activity related to network side is performed in constructor. Also, this error is returned as text message only, no codes provided.

Is it safe just to recreate connection object ? Actually it's very pour idea, as connection instance is passed to many structures in the program...

[ovidiu-balau]

Still an issue, anyone found any solution for this?

[jovemnf]

me too

[coder77]

I got this as well

[sidorares]

Hey @coder77 @jovemnf @ovidiu-balau @Akash0333 @zebullax and everyone else experience issue - is there a reliable way to reproduce? Ideally dockerized self contained , or at least sequence of instructions

[nag944]

Hey @coder77 @jovemnf @ovidiu-balau @Akash0333 @zebullax and everyone else experience issue - is there a reliable way to reproduce? Ideally dockerized self contained , or at least sequence of instructions

It's extremely easy to reproduce - just start a connection and wait for default timeout (you can emulate it changing default mysql timeout setting to some smaller value - by default it's around 8 hours).

Btw, I tested it not on pool, but with raw connection - in this case error object is empty at all, only text message is present.

As I checked source code, seems this problem has no way to be solved in current state. When mysql connection is gone away, the network connection instance data inside the component is destroyed, so you cannot just restart the net - must create a new connection instance at all (network connection instance is created in the constructor !!!).

[sidorares]

As I checked source code, seems this problem has no way to be solved in current state.

Yes, individual Connection is not meant to survive underlying transport reconnect, it's 1:1 mapping to mysql connection / thread with same possible states ( authentication, change user, etc ). When network connection dies or actively closed by server it should not be re-used, this is just how it is designed. If it's your issue the solution should be in your code ( listen for error / end events etc ). If you want simpler api just use pool - it should handle all this

[nag944]

But as I see previous messages, pool seems to have same problems ?

The only way I found is just send stub queries every hour to keep connection alive. In the other case, I cannot even make correct destruction - it hangs forever, so must forget about the instance and create new one - rely that garbage collector makes it's job right.

[zebullax]

Unless I'm mistaken, issue is not limited to single connection but also connection managed by pool (that s what I was using as a setup), and that would make sense since the pool is only managing creating and yielding connection, if an existing connections has been remotely closed, and is yielded by the pool, we get the same error that if we were working with a simple connection

[sidorares]

pool seems to have same problems ?

I'm happy to try to investigate, if I have simple way to reproduce with pool that would help me a lot

if an existing connections has been remotely closed, and is yielded by the pool, we get the same error that if we were working with a simple connection

In the case of pool it's different in terms of who is responsible for tracking and recycling of dead connection. Pool should do it all for you while when you use individual connection you must listen for error ( both on connection and on query results )

[MR4online]

having the same problem with pooled connections.
If our MySQL stats are correct, the count of the active connections does not vary over time. The last time the error occured was when an cron caused many queries. May be the aggregation of new connection doenst work as expected. Is there an way to debug that?

Our code looks pretty much like in the docs https://github.com/mysqljs/mysql#pooling-connections. The most logic is simple (get new Connection from permanent Pool, query it, release it).

[ADumaine]

I'm using a single connection and after the connection is lost con.end() or con.release() throws the error.
My current solution is to set the connection object to null and create a new connection on the error.

if (con) con=null
initcon(); //sets up the connection

[T1MOXA]

I have the same problem

[Xsmael]

Mee toO! any fix ?

[juaneuro90]

Guys I'm having the same issue.

[jmordica]

Me too.

[jmordica]

Switching to createPool resolved my issue.

[franck-nadeau]

Switching to createPool resolved my issue.

@jmordica What do you mean by switching to createPool? That is what I am using and I am getting this problem. Also that is what Rakeshvishnoi029's initial code used. Are you passing something special as an option?

[zeeshanaligold]

Hi @jmordica

I have the same issue. Can you please tell how did you get it fix? Thx

const mysql = require('mysql2');

const con = mysql.createConnection({
    host     : process.env.RDS_HOSTNAME,
    user     : process.env.RDS_USERNAME,
    password : process.env.RDS_PASSWORD,
    database : process.env.RDS_DATABASE
});

[coplayer]

I have same issue!

[rudiantotiofan]

any update for this issue ? i got the same problem.

Thanks,

[davehorton]

Same issue here, using createPool. Any updates on this?

const pool = mysql.createPool({
  host: process.env.XX,
  user: process.env.XXX,
  password: process.env.XX,
  database: process.env.XX,
  connectionLimit: process.env.XX
});

"err":
{"type":"Error","message":"Can't add new command when connection is in closed state","stack":"Error: Can't add new command when connection is in closed state\n    
at PoolConnection._addCommandClosedState (/../mysql2/lib/connection.js:137:17)\n    
at PoolConnection.query (../mysql2/lib/connection.js:502:17)\n    
at getMysqlConnection (../login.js:31:10)\n    
at process.nextTick (../mysql2/lib/pool.js:44:37)\n    
at process._tickCallback (internal/process/next_tick.js:61:11)","fatal":true},"msg":"Error getting db connection","v":1

at least update the docs to remove the misleading information that pool handles this for you.

[sidorares]

@davehorton unfortunately not enough information to debug. Can you make a test repo to reproduce?

This can happen if you call .end() on connection and later try to call .execute() this might be a logic error in your application but could be a bug in the driver, unfortunately not enough information to tell

[runekm]

_(EDIT: I see that I was running an old version of the package (1.7.0). I tried upgrading to the newest vesrion. I don't know if there is any difference yet)_

I too get this error from time to time. I haven't investigated the problem in detail, but it seems to occur every couple of days, and lasts untill the app is restarted.

The connection pool is created with:

var mysql = require('mysql2');
pool = mysql.createPool({
    connectionLimit : config.DB_CONNECTION_LIMIT,
    host            : config.DB_HOST,
    port            : config.DB_PORT,
    user            : config.DB_USER,
    password        : config.DB_PASSWORD,
    database        : config.DB_DATABASE,
    dateStrings     : true,
    debug           : config.DB_DEBUG
});
promisePool = pool.promise();

The error occurs when I run:
[ results, fields ] = await connection.query( sql );
after running:
connection = await promisePool.getConnection();
which gives no error.

The error says:
Error: Can't add new command when connection is in closed state at PromisePoolConnection.query ([path_to_my_app]/node_modules/mysql2/promise.js:92:22

[d3rf]

I solved in a class like doing something like that.

`class MySQLExec {
//propriedades e funções da classe aqui
constructor() {

}
conecta(){
  this.conn =  mysql.createConnection({
    host: '',
    user: '',
    database: '',
    password:config.password,
    multipleStatements: true
   // waitForConnections: true
  });  
}
run(query,rows) {
    var returnResult = null;
   //connect
    this.conecta();
    this.conn.query(
        query,
        rows,
        function(err, results, fields) {
          returnResult = results;              
         // results contains rows returned by server

         if (err){
           console.error(err)
         }

         // console.log(fields); // fields contains extra meta data about results, if available
         // return results;
          // If you execute same statement again, it will be picked from a LRU cache
          // which will save query preparation time and give better performance
          //
        }
      );
     //disconnect after run
      this.conn.end();
      //this.conn.end();
      return returnResult; 
}

}`

[skorolev-tjc]

I had the same issue with pooled connections and 8.0.15 server and to make things worse I had also long running connections. For now I have a cautious optimism that the issue is resolved. Some changes must be done in your logic:

1. If you need long running connections then first check MySQL server parameter wait_timeout - it is in seconds and it will kill silent connections after the period of time, probably it will be sufficient for you just to increase this value
2. If wait_timeout is not sufficient you can use connection.ping method with some interval - it will keep the connection alive
3. Do analyse error object after operations: if it has fatal = true then the connection became useless (probably it was closed somehow or connection was lost) - you must call connection.destroy() - it will remove this poisoned connection from the pool otherwise the pool can provide this connection again – probably some change should be implemented in the pool logic.

[gaurav16694]

is there any update on this issue either how to reproduce it or fix it ?

[HsinHeng]

In my case, I perform insertMany exceed 30,000 records at one time.
The error was produced.

I resolved my issue by increase max_allowed_packet in mysql server config.
because the transmission bytes are too large.

[sidorares]

@HsinHeng interesting, maybe that can be solved at driver level. We should be able to automatically detect max_allowed_packet ( from settings or by querying variable immediately after connection ) and then splitting outgoing packet into smaller packets - see https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html

mysql2 does support large incoming packet sizes ( see https://github.com/sidorares/node-mysql2/blob/442d3042245c761eaeafe36b0e35d45cb3f87025/lib/packet_parser.js#L83-L97 ) but your issue is probably related to packet length > max_allowed_packet and not 16Mbyte packets. I need to double check maybe max_allowed_packet can be automatically sent at connection but if not still can be queried using .query()

[sidorares]

@HsinHeng what was exactly error code in your case? Having some help in error message would be useful I guess. Something along the lines if(error.code === errors.ER_NET_PACKET_TOO_LARGE) { error.message = "Packet too large. Check max_allowed_packet system/session variable. See [wiki url with more info] for more information" }

[HsinHeng]

@HsinHeng what was exactly error code in your case? Having some help in error message would be useful I guess. Something along the lines if(error.code === errors.ER_NET_PACKET_TOO_LARGE) { error.message = "Packet too large. Check max_allowed_packet system/session variable. See [wiki url with more info] for more information" }

@sidorares
You got it!

Here is my error details.

"name":"SequelizeDatabaseError","parent":{"code":"ER_NET_PACKET_TOO_LARGE","errno":1153,"sqlState":"08S01","sqlMessage":"Got a packet bigger than 'max_allowed_packet' bytes" }

Hope you could resolve this issue.

[sidorares]

@HsinHeng well in your case error text from the server already had some hints

What we can do is have maxAllowedPacketSize connection option that maybe is "auto" by default. When set to auto a query is performed immediately on connection to get max_allowed_packet variable ( downside: slightly worse latency until first "useful" response, especially when connection is always shoer lived )