ERR_STREAM_WRITE_AFTER_END when executing query with MySQL2 running on Node.js

Question

I'm implementing a functionality which should upon a click on a button extend an user session lifespan by sending a request to the backend and to update the database using MySQL2.

In order to do that I wrote the following front-end code:

onClose: function (oAction) {
    try {
        if (oAction == "YES") {
            let reqURL = "/sessionExtend";
            let reqData = {
                session_id: sessionStorage.getItem("SessionId"),
                user_id: sessionStorage.getItem("UserId")
            };
            let callbackOK = function (responseData) {
                curr.onSuccessfulResponse(curr, responseData, "sessionExtendSuccess", "sessionExtendFail", "", false);
            };
            let callbackErr = function (responseData) {
                curr.onErrorResponse(curr, responseData, "sessionExtendFail");
            };

            curr.performRequest(reqURL, reqData, callbackOK, callbackErr);
        }
    } catch (err) {
        console.log(err);
        MessageToast.show(sMsg);
    }
}

The request is received by app.js, which establishes the database connection using MySQL2 and forwards the request to the DAL:

app.post("/sessionExtend", async function (req, res) {

    let session_id = req.body.session_id;
    let user_id = req.body.user_id;

    let con = DAL.getConnection();

    res.setHeader("Content-Type", "application/json");

    try {

        const response = await DAL.sessionExtend(con, session_id, user_id);

        res.send(JSON.stringify({
            "result": true,
            "message": "session extended"
        }));

    } catch (e) {

        res.send(JSON.stringify({
            "result": false,
            "message": "can not extend session"
        }));

    }

    con.close();

});

The DAL module executes the SQL-query and should return the result either success or error:

sessionExtend: async function sessionExtend(con, session_id, user_id) {

    con.connect(function (err) {
        try {
            if (err) throw err;
            con.query(qryDict.SQL_QUERIES.setUpdateExtendSession, [session_id, user_id], function (err) {

                let result;

                if (err) {
                    result = JSON.stringify({
                        "result": false,
                        "message": "failure"
                    });
                } else {
                    result = JSON.stringify({
                        "result": true,
                        "message": "success"
                    });
                }

                return result;

            });
        } catch (err) {
            let result = JSON.stringify({
                "result": false,
                "message": err
            });

            return result;
        }
    });
},

The problem is that when I execute this code in debugger, I get an exception:

ERR_STREAM_WRITE_AFTER_END Error [ERR_STREAM_WRITE_AFTER_END]: write after end at Socket.Writable.write (_stream_writable.js:297:11) at Connection.write (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\connection.js:226:17) at Connection.writePacket (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\connection.js:271:12) at ClientHandshake.sendCredentials (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\commands\client_handshake.js:64:16) at ClientHandshake.handshakeInit (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\commands\client_handshake.js:137:12) at ClientHandshake.execute (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\commands\command.js:39:22) at Connection.handlePacket (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\connection.js:417:32) at PacketParser.onPacket (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\connection.js:75:12) at PacketParser.executeStart (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\packet_parser.js:75:16) at Socket. (C:\Users\User\IdeaProjects\TST\node_modules\mysql2\lib\connection.js:82:25)

I also paid attention that during the debugging, I firstly get on a frontend a response from the backend and only then I reach the breakpoints in DAL withing con.query(qryDict.SQL_QUERIES.setUpdateExtendSession, [session_id, user_id], function (err) {…}.

My questions:

1. Why do I get ERR_STREAM_WRITE_AFTER_END and how to avoid it?

2. Why do I firstly get on the frontend a response from the backend and only then I reach the breakpoints in DAL? I assumed that await DAL.sessionExtend(con, session_id, user_id) should wait until the task on a DAL will be done and a promise will be resolved.

Answer 1

With a help of CherryDT, the problem has been solved by switching to ES7 async/await-wrapper version of MySQL2 — mysql2/promise.

To save a time of the rest of the public, the final ready-to-use code:

app.js

app.post("/sessionExtend", async function (req, res) {

    let session_id = req.body.session_id;
    let user_id = req.body.user_id;

    const con = await DAL.getConnection();

    res.setHeader("Content-Type", "application/json");

    const response = await DAL.sessionExtend(con, session_id, user_id);

    res.send(JSON.stringify({
        "result": response.result,
        "message": response.message
    }));

    await con.close();

});

DAL.js

sessionExtend: async function sessionExtend(con, session_id, user_id) {

    let result;

    const [rows, fields] = await con.execute(qryDict.SQL_QUERIES.setUpdateExtendSession, [session_id, user_id]);

    if (rows.warningStatus === 0) {
        result = {
            "result": true,
            "message": "session extended"
        };
    } else {
        result = {
            "result": false,
            "message": "session is not extended"
        };
    }

    return result;

},

As you may see, now the code is a way easier to understand and maintain.

P.S. My advise: use the async/await, they are wonderful and try to avoid callbacks as much as possible.

Answer 2

In short: You are not awaiting the con.connection and con.query, so the outer code continues and calls con.close and returns the frontend result, and later on con.query attempts to send a query through the now-closed connection, leading to this exception.

You are writing async functions but you only made them "half-async".

For example, this won't work:

async function getStuff () {
  stuff.get(function (err, data) {
    if (err) throw err // kills your process if it happend!
    return data.stuff // returns to nowhere
  })
}

// later on:
const stuff = await getStuff()
console.log(stuff) // prints undefined!

...because essentially, your async function just synchronously calls another function( not waiting for it) then immediately returns nothing (i.e. undefined):

async function getStuff () {
  stuff.get(...)
  // as you can see, no return inside getStuff
}

And later on, the callback you passed will run, but then your outer code's train has already left the platform.

What you'd want to do instead is have stuff.get return a promise (most modern libraries will do that even if they additionally expose a callback promise for compatibility with older codebases) and await it:

async function getStuff () {
  const data = await stuff.get() // waits for the stuff to come back
  return data.stuff // actually returns the stuff
  // The `if (err) throw err` now became unnecessary as well
}

// later on:
const stuff = await getStuff()
console.log(stuff) // prints the stuff!

If your SQL library would expose a promise interface, you could simply await it. You wrote that you are using mysql2. This library has a promise interface if required with require('mysql2/promise'). I'd suggest switching to the promise interface instead of the callback interface!

There is also a way to "upgrade" an existing con connection to the promise interface: con.promise(). So you'd simply do con = DAL.getConnection().promise() instead of con = DAL.getConnection().

Then, you could rewrite the code like this (or equivalent, depending on which library you pick):

async function sessionExtend(con, session_id, user_id) {
    try {
        await con.connect()
        await con.query(qryDict.SQL_QUERIES.setUpdateExtendSession, [session_id, user_id])
        return JSON.stringify({ result: true, message: 'success' })
    } catch (err) {
        return JSON.stringify({ result: false, message: err.toString() })
    }
}

---

EDIT: The following part is actually obsolete because mysql2 allows upgrading an existing connection to the promise interface, but I'll leave this here anyway in case it helps someone else in a similar situation!

If you can't switch to the promise interface, you could instead promisify the existing calls (it just looks a bit more convoluted though):

const { promisify } = require('util')

async function sessionExtend(con, session_id, user_id) {
    try {
        await promisify(con.connect).call(con)
        await promisify(con.query).call(con, qryDict.SQL_QUERIES.setUpdateExtendSession, [session_id, user_id])
        return JSON.stringify({ result: true, message: 'success' })
    } catch (err) {
        return JSON.stringify({ result: false, message: err.toString() })
    }
}

util.promisify wraps a function that expects an (err, data) callback, converting it into an async function that returns a promise instead. Since con.query et al. are methods on con though, they need to keep that context, that's why I wrote promisify(con.query).call(con, ...) instead of just promisify(con.query)(...).