tedious: Library (node-mssql) doesnt recover after db outage

Hey @madal3x, this issue is fixed by PR https://github.com/tediousjs/tedious/pull/769. I tested the PR with your repro code in docker, driver throws connection lost error and cleans things up. Issue you see in tediousjs/node-mssql should vanish once #769 gets merged.

Are you referring to SQL Server in this line?

The db is a docker container that i stop through kitematic.

When I mention db, I’m referring to the individual database inside the Server. Can you share tedious debug logs? Enable them in connection config

options: {
	debug: {
	packet:  true,
	data:  true,
	payload:  true,
	token:  true,
	log:  true
	}
}

@Suraiya-Hameed yes, true, though i thought one would replace the connection if it had an error. So i recreated the scenario just by using tedious, starting the script when db is up, then stopping the db and starting it again while the script was running. And the results were that after the db was up, the library kept outputting the same error:

RequestError: Requests can only be made in the LoggedIn state, not the Final state at RequestError (/Users/alex/projects/sofy-unit2/node_modules/tedious/lib/errors.js:34:12) at Connection.makeRequest (/Users/alex/projects/sofy-unit2/node_modules/tedious/lib/connection.js:1584:26) at Connection.execSql (/Users/alex/projects/sofy-unit2/node_modules/tedious/lib/connection.js:1347:12) at Promise (/Users/alex/projects/sofy-unit2/testpromise/app-tedious.js:66:24) at new Promise (<anonymous>) at Repo.names (/Users/alex/projects/sofy-unit2/testpromise/app-tedious.js:17:16) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7)

In the process i’ve discovered that the request.on(‘done’) was not called, but the doneInProc and doneProc were called. Unfortunately these were called with the value of rows being an empty array. Therefore I had to aggregate the rows inside the row event for request.

Code:

app.js

const Connection = require('tedious').Connection;
const Request = require('tedious').Request;

class Repo {
    constructor(connection) {
        /**
         * @private
         * @type {Promise<Connection>}
         */
        this.connection = connection;
    }

    async names() {
        const connection = await this.connection;
        const allRows = [];

        return new Promise(((resolve, reject) => {
            const request = new Request("select name from names", function(err, rowCount) {
                if (err) {
                    return reject(err);
                } else {
                    console.log(rowCount + ' rows');
                }
            });

            request.on('row', function(columns) {
                console.log('onRow');

                columns.forEach(function(column) {
                    const row = [];
                    row.push({
                        metadata: column.metadata,
                        value: column.value,
                        toString: () => column.value
                    });
                    allRows.push(row);
                });
            });

            /*
            request.on('done', function(rowCount, more) {
                console.log('onDone');

                return resolve(rows);
            });
            */

            /*
            request.on('doneInProc', function (rowCount, more, rows) {
                console.log('onDoneInProc');

                console.log(rows);

                return resolve(rows);
            });
            */

            request.on('doneProc', function (rowCount, more, returnStatus, rows) {
                console.log('onDoneProc');

                //console.log(rows);

                return resolve(allRows);
            });

            connection.execSql(request);
        }));
    }
}

/**
 *
 * @return {Promise<Connection>}
 */
function createConn() {
    const user = 'sa';
    const password = 'P@55w0rd';
    const server = 'localhost';
    const database = 'test';

    const config = {
        server: server,
        userName: user,
        password: password,
        options: {
          debug: {
            packet: true,
            data: true,
            payload: true,
            token: false,
            log: true
          },
          database: database,
          //encrypt: true // for Azure users
        }
    };

    const connection = new Connection(config);

    connection.on('errorMessage', err => {
        console.error('onError')
        //console.error(err);
    });

    return new Promise(((resolve, reject) => {
        connection.on('connect', err => {
            if (err) {
                console.error('onConnectionError');

                reject(err)
            } else {
                resolve(connection);
            }
        });
    }));
}

module.exports = (
    intervalSeconds,
    repo = new Repo(createConn())
) => {
    const main = () => run().catch(console.error);
    main();
    setInterval(
        main,
        intervalSeconds * 1000
    );

    async function run() {
        console.log("next");

        await repo.names()
            .then(res => console.log(`names res: ${res}`))
            .catch(err => console.error(`names err: ${err.stack}`));
    }
};

index.js

const app = require('./app');
app(3);