Mysql: Unhandled 'error' event with connections

[Steague]

Probably related to #2049

Error:

events.js:183
      throw er; // Unhandled 'error' event
      ^

Error: Connection lost: The server closed the connection.
    at Protocol.end (/Users/steagu/src/parc-aprmds-warm-storage-api/node_modules/mysql/lib/protocol/Protocol.js:113:13)
    at Socket.<anonymous> (/Users/steagu/src/parc-aprmds-warm-storage-api/node_modules/mysql/lib/Connection.js:109:28)
    at emitNone (events.js:111:20)
    at Socket.emit (events.js:208:7)
    at endReadableNT (_stream_readable.js:1064:12)
    at _combinedTickCallback (internal/process/next_tick.js:138:11)
    at process._tickDomainCallback (internal/process/next_tick.js:218:9)

Versions:

$ npm -v connect-mysql
6.1.0
$ node -v
v8.11.2
$ npm -v mysql
6.1.0

The error occurs after leaving the app running for many hours and then attempting a new query. The code below is what I am using:

Code:

import dotenv from 'dotenv';
import mysql from 'mysql';

dotenv.config();

let _instance = null;

export default class Mysql {
    constructor() {
        if (!_instance) {
            _instance = this;
        }

        this._type = 'DB';
        this._conn = mysql.createConnection({
            host     : process.env.MYSQL_HOST,
            user     : process.env.MYSQL_USER,
            password : process.env.MYSQL_PASSWORD,
            database : process.env.MYSQL_DATABASE
        });

        return _instance;
    }

    get type() {
        return this._type;
    }

    set type(value) {
        this._type = value;
    }

    async query(sql, arr = []) {
        return await new Promise((resolve, reject) => {
            this._conn.config.queryFormat = (query, values) => {
                if (!values) return query;
                return query.replace(/:(\w+)/g, (txt, key) => {
                    if (values.hasOwnProperty(key)) {
                        return mysql.escape(values[key]);
                    }
                    return txt;
                });
            };

            this._conn.query(sql, arr, (err, results, fields) => {
                if (err) {
                    reject(err);
                    return;
                }

                resolve({
                    results,
                    fields
                });
            });
        });
    }
}

[dougwilson]

So I think you're getting the unhandled error message because I don't see you adding an 'error' event handler to the connection object.

From https://github.com/mysqljs/mysql#error-handling

Last but not least: If a fatal errors occurs and there are no pending callbacks, or a normal error occurs which has no callback belonging to it, the error is emitted as an 'error' event on the connection object. This is demonstrated in the example below:

Note: 'error' events are special in node. If they occur without an attached listener, a stack trace is printed and your process is killed.

If you add the following below this._conn = mysql.createConnection

        this._conn.on('error', function (err) {
            console.log('caught this error: ' + err.toString());
        });

Is the error caught?

[Steague]

I have added your suggested code. I will need to wait some hours to see if the error is caught. Thank you in advance!

[dougwilson]

No problem! Let me know. I'll keep this open to hear back.

[Steague]

So the error as reported is gone. but now a different functionality is happening.

1. After a long period of inactivity. (As well as running the node server off my local laptop which I close and then reopen in the morning)
2. I attempt an action that triggers a query that, after 30 seconds, results in an Error: read ETIMEDOUT
3. I attempt the same action and get a different error: Error: Cannot enqueue Query after fatal error.

At this point I have to restart my node service to be able to establish a new connection to mysql.

How can I get the app back into a safe state?

[Steague]

Also, is there a way to trigger a fatal error so that the enqueuer can't then validate so I can test said resetting to a safe state?

[dougwilson]

So when you close your laptop the tcp connection is severed which is why that happens. You have to create a new connection when that happens.

[Steague]

@dougwilson good to know. Are there any hooks to determine the state of the tcp connection within the mysql package ( did look at the documentation but am not seeing anything sticking out to me)?

[dougwilson]

So Node.js doesn't really provide any hooks for us to expose. That's why you noticed that it sat there until a timeout just to know the connection is bad. If you know of a way to check this in Node.js sockets, I'd love to add it!

[Steague]

In that case, I will just catch the error message directly and attempt to reopen/connect -> retry the query. Thank you for your help. This issue can be closed.

[neoadventist]

@Steague did that work? I have the same error and am interested in a good solution.