Closing connection throws error Quit inactivity timeout

[czioutas]

Hello, for the purpose of my application I need to create an one-off connection do a query and then close.
Everything works fine except the closing part. As you can see in the debug output it sends a ComQuitPacket (i think that is enough) but the connection is never closed until it times out from the db itself.

I dont understand if I am closing the connection in a wrong way or there is something I am missing. Is it because it is closed within the callback?

For a moment I thought its an issue similar to #1236 but no luck.

Node 5.5.0 && 4.2.6 && 4.2.1 (I use nvm to handle the versions)
Node-mysql 2.10.0
MysqlDB 5.5.40
Database Hosted on Heroku (ClearDB MySQL :: Database) Havent tested with other db.

  var connection = mysql.createConnection({
    debug: true,
    host: mysql.host,
    user: mysql.username,
    password: mysql.password,
    database: mysql.database
  })

  connection.query(enhancedWidgetModel.query, function (err, rows, fields) {
    connection.end(function (err) {
      console.log(err) // this is caught
    })
    if (err) {
      console.log(err)
      callback({
        data: null,
        msg: 'error',
        code: 'error'
      })
    } else if (rows) {
      callback({
        data: rows,
        msg: 'acknowledged',
        code: 'success'
      })
    }
  })

Debug output

<-- RowDataPacket
RowDataPacket { month: 'April', value: 13 }

<-- EofPacket
EofPacket {
  fieldCount: 254,
  warningCount: 0,
  serverStatus: 34,
  protocol41: true }

--> ComQuitPacket
ComQuitPacket { command: 1 }

{ [Error: Quit inactivity timeout]
  code: 'PROTOCOL_SEQUENCE_TIMEOUT',
  fatal: true,
  timeout: 30000 }

[aybmab]

👍

[czioutas]

Any updates on this? @dougwilson

[dougwilson]

@Drakoumel, no I have not had any time to investigate. Please feel free to investigate and provide updates into your investigation and feel free to propose fixes!

[czioutas]

@dougwilson I setup a mysql database on a self hosted server (not heroku) and the connection closed gracefully.

I will contact them in order to see what type of firewall or connection management configurations they have that cause this issue.
I will close this issue after the reply of heroku so we can keep the info anyone else has the same issue.

[armin-github]

I was facing the same error (Quit inactivity timeout) with the mysql module (version 2.10.2) and MySQL server on Cleardb. I tried various node versions including 4.2.2 where some people had reported to be running fine, but that did not solve the issue for me. Here is what I did: I used a connection pool instead of using single connections. The nice thing about using a connection pool is that the Mysql server both removes the connection after its default timeout has reached and creates new connections and puts them into the pool whenever needed. Here is the general idea as to how the pool should be used:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 5,
  host            : 'the_host',
  user            : 'username',
  password        : 'pass'
});

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query( 'SELECT columnX FROM tableX', function(err, rows) {
    // Always release the connection back to the pool after the (last) query.
    connection.release();

    // Don't use the connection here, it has been returned to the pool.
  });
});

However, if you already have several connection.query(...) calls in your code, as I did, you might want to use Adam Yost's wrapper posted on:
https://stackoverflow.com/questions/17015590/node-js-mysql-needing-persistent-connection/30914967#30914967?newreg=df8cfe8f06a04593acac50a200c6fd71
. Adma's code creates and uses the connection pool while no changes are needed to be made to the code that already exists. I used it myself and was quite pleased about it!

[czioutas]

Response from Cleardb:

Hello,

We have noted some issues with nodeJs in this respect. Unfortunately we have no recommended fix except to make sure you're using the most recent version of the package. I'm sorry I can't be more helpful.

Michael Landis
Manager of Global Operations for ClearDB

[YOnoda]

Any update on this? I use Node 6.0.2 but still got this problem.

[dougwilson]

Hi @YOnoda, I don't have a ClearDB account, so I can't look into this issue myself. If anyone can debug it and provide insights or a fix, that would probably be the only way to move this forward. From the initial post, it shows the issue is that this module is sending the Quit packet to the server, but the server does not follow that up with one of the following actions within 30 seconds:

1. ClearDB needs to respond with a Ok packet, or
2. ClearDB needs to close down the TCP connection

[aybmab]

Just a general comment - I started using postgres and sequelize and stopped having productions issues after pushing via heroku. I know it's not a fix, but it avoids these random problems introduced by ClearDB.

[b6t]

+1 Still having issues with this while using connection pooling as @armin-github described above. I am making 3 async db calls 1 returning 8.5k rows and the other 2 returning 600 rows each. Lowering the amount of data that is being returned seems to fix the issue.

On other occasions, using a small amount of data. New Relic found that 98.99% of the time was being spent in poolCluster.getConnection(). The subsequent db query requests run quickly as normal.

I made my db calls synchronous and it seems to have relieved the error, but of course makes the total response time of the call much slower. This isnt a solution, just an observation.

[dougwilson]

I'm going to close this, since no one has provided the additional information I need to look into it, so I'm not going to just leave it open forever. The best bet to move this forward is to provide a PR that fixes the issue :) !

[PanMan]

I'm fairly sure this is a non-standard thing ClearDB does.. Have experienced it a few times, sofar unfortunately my solution has been to switch to another hosted DB provider... :(

[kevincolten]

ClearDB still doing this. Moving to the JawsDB Maria (on Heroku) corrected the issue for me.