Cannot enqueue Query after fatal error

[viki53]

Hi, I keep getting this error when I try to insert a new row in a table. Everything worked fine before and I didn't modify the query recently.

More details about the error stack:

Cannot enqueue Query after fatal error.
    at Protocol._validateEnqueue (mysql/lib/protocol/Protocol.js:155:16)
    at Protocol._enqueue (mysql/lib/protocol/Protocol.js:104:13)
    at PoolConnection.Connection.query (mysql/lib/Connection.js:166:25)

Thing is I don't see anything about a fatal error anywhere in the logs.

Any idea what may be the problem?

[dougwilson]

Hi! This is actually normal behavior, as it will occur when you try to query on a connection once a fatal error has occurred.

You may be calling a method on your connection like query sometime earlier in your code and are accidentally not checking the returned error. Another possibility is you are checking the errors, but not checking if err.fatal is true and discarding the connection.

Unfortunately there are too many ways in which you may be missing the earlier fatal error. Please feel free to reopen this issue if you have any of the following:

1. A JS file and MySql schema I can run and reproduce your issue.
2. A patch that fixes the issue, if there is one.
3. A detailed description about somewhere in this module that is not correctly bubbling up an error so you do not see it and have a chance to discard your connection prior to the error you pasted, if this exists.

[sidorares]

I suggest to search node-mysql source code for err.fatal = true and insert console.trace() before each. It's strange that it's not visible in your output, but you might missing some error check somewhere

[dougwilson]

Another thing to note is that if you do not pass a callback to conn.query, then it purposely makes errors silent. You may also want to make sure that you are always providing a callback.

[dougwilson]

Assuming you are using the latest version, try adding this to your code after createConnection to see if it helps you troubleshoot:

var connection = mysql.createConnection(...);
var del = connection._protocol._delegateError;
connection._protocol._delegateError = function(err, sequence){
  if (err.fatal) {
    console.trace('fatal error: ' + err.message);
  }
  return del.call(this, err, sequence);
};

[viki53]

Ok, I just added the code @dougwilson provided and it looks like I was looking the right way: the connection was lost, event though I'm using a Pool.

Connection lost: The server closed the connection.
at Protocol.dbConnection._protocol._delegateError (/application/mmf-chat.js:92:12)
at Protocol.end (/application/ovh-var/node/mysql/lib/protocol/Protocol.js:82:8)
at Socket. (/application/ovh-var/node/mysql/lib/Connection.js:81:28)
at Socket.EventEmitter.emit (events.js:117:20)
at _stream_readable.js:920:16
at process._tickCallback (node.js:415:13)

I am already using a callback in every query, but obviously this error is not caused by a query.

Any way I can avoid being disconnected from the DB server?

BTW, here's my config:

var dbConfig = {
    host: 'server_name',
    user: 'my_username',
    password: 'my_password',
    database: 'db_name',
    connectionLimit: 50,
    queueLimit: 0,
    waitForConnection: true
};
var database = mysql.createPool(dbConfig);

[sidorares]

what do you mean by "avoid being disconnected from the DB server" ? You should never assume network 100% of time reliable and handle reconnects

[viki53]

The Db server is in the same datacenter, so network should be pretty reliable (at least 98% of the time).

Is there any way to automatically reconnect (or at least try) when disconnected? As that's what the pool should be doing...

[dougwilson]

The Db server is in the same datacenter, so network should be pretty reliable (at least 98% of the time).

Your own MySQL server will also disconnect your connections if they exceed certain settings on your server, like idle timeout.

Is there any way to automatically reconnect (or at least try) when disconnected?

No, you must make a new connection. Since a connection has state on the server, if you "reconnect" and connection, you'll loose that state, which would be terrible (think reconnecting a lost connection in the middle of a transaction certainly won't work).

As that's what the pool should be doing...

Yes, it should. In fact, version 2.2+ should not allow you to ever acquire a bad connection from the pool. You may have acquired a connection and held it for a long time, and so it disconnected while it was outside the pool.

@viki53 what version of this module are you using (it hasn't been established yet)? What version of node.js are you using?

[viki53]

I'm using node-mysql v2.3.x (basically the last one, as I started using it only this week) and Node v0.10.22.

In fact my app is basically a real time chat (with game-related notifications to come) running with Socket.io.

When the DB is connected I start running Socket.io and make queries when needed:

var database = mysql.createPool(dbConfig);
database.getConnection(function(err, dbConnection) {
    if(err) {
        console.error('MySQL — Error connecting: ' + err.stack);
        return;
    }

    console.log('MySQL — Connected');

    var usernames = {};
    io.on('connection', function (socket) { /* Start waiting for users to use the chat */

I already moved some queries onto a PHP server (using HTTP requests to retrieve data) but it won't be as efficient if I move them all to PHP.

I understand keeping a connection active for an unlimited period would be suicidal, but if I could find a way to process queries without worrying whether the DB is disconnected or not, that would be really great.

[dougwilson]

but if I could find a way to process queries without worrying whether the DB is disconnected or not, that would be really great.

Ah, I see now. So what you are doing is using pool, but only ever using a single connection. You need to change your pool use is all.

var database = mysql.createPool(dbConfig);

var usernames = {};
io.on('connection', function (socket) {
  /* Start waiting for users to use the chat */
  // somewhere down here when you need to make a query, do this:
  database.getConnection(function(err, dbConnection) {
    if (err) { /* do something */ return }
    dbConnection.query(sql, function(err, rows) {
      dbConnection.release() // return to the pool
      if (err) { /* do something; MUST be after release call */ return }
      // stuff
    });
  })
})

[dougwilson]

but if I could find a way to process queries without worrying whether the DB is disconnected or not, that would be really great.

You just have to actually use the pool. From your example code, you are not using the connection pooling; you are just acquiring a single connection forever, in which case you may as well not even use the pool at all.

You need to call database.getConnection all over your code when you need a connection to query, not just once at the start.

[viki53]

Oh, I didn't understand it like that from the doc, my bad!

I'll refactor my code then and let you know if anything new comes up. Thanks!

[EDIT] Ok, looks much better now. Thanks a lot folks! And sorry I misread the doc...

[tsuz]

Hi, I think this issue is similar to mine:

1. Connect to MySQL
2. End MySQL service (should not quit node script)
3. Start MySQL service, Node reconnects to MySQL
4. Query the DB -> FAIL (Cannot enqueue Query after fatal error.)

I solved this issue by recreating a new connection with the use of promises (q).

mysql-con.js

'use strict';
var config          = require('./../config.js');
var colors          = require('colors');
var mysql           = require('mysql');
var q               = require('q');
var MySQLConnection = {};

MySQLConnection.connect = function(){
    var d = q.defer();
    MySQLConnection.connection = mysql.createConnection({
        host                : 'localhost',
        user                : 'root',
        password            : 'password',
        database            : 'database'
    });

    MySQLConnection.connection.connect(function (err) {
        if(err) {
            console.log('Not connected '.red, err.toString().red, ' RETRYING...'.blue);
            d.reject();
        } else {
            console.log('Connected to Mysql. Exporting..'.blue);
            d.resolve(MySQLConnection.connection);
        }
    });
    return d.promise;
};

module.exports = MySQLConnection;

mysqlAPI.js

var mysqlCon        = require('./mysql-con.js');
mysqlCon.connect().then(function(con){
   console.log('connected!');
    mysql = con;
    mysql.on('error', function (err, result) {
        console.log('error occurred. Reconneting...'.purple);
        mysqlAPI.reconnect();
    });
    mysql.query('SELECT 1 + 1 AS solution', function (err, results) {
            if(err) console.log('err',err);
            console.log('Works bro ',results);
    });
});

mysqlAPI.reconnect = function(){
    mysqlCon.connect().then(function(con){
      console.log("connected. getting new reference");
        mysql = con;
        mysql.on('error', function (err, result) {
            mysqlAPI.reconnect();
        });
    }, function (error) {
      console.log("try again");
        setTimeout(mysqlAPI.reconnect, 2000);
    });
};

I hope this helps.