Error when getting connection from pool

[fabiobotsilva]

I have an express rest api, and sometimes I get a connection error after the requests, then I made an example to reproduce the error. I would appreciate it if anyone knows how to avoid this error and can share, or confirm if it's really a bug.

I am aware of the asynchronous nature of the example, the position of the error varies, sometimes it is at the beginning, in the middle or at the end, sometimes everything works well. Please, try again if it works at the first time.

(Please, adjust the connection data for your database)

Thanks,

I'm using: node: v20.15.1 node-firebird: 1.1.9

            const dbOptions: firebird.Options = {
                host: config.DB_HOST,
                port: config.DB_PORT,
                database: config.DB_DATABASE,
                user: config.DB_USER,
                password: config.DB_PASSWORD,
                lowercase_keys: true,
                pageSize: 8192,
                retryConnectionInterval: 1000
            };

            const dbPool = firebird.pool(20, dbOptions);
            console.log('--------------');
            console.log(' Pool created ');
            console.log('--------------');

            [0,1,2,3,4,5,6,7,8,9].forEach((elem: number) => {
                dbPool.get(function (err, db) {
                    if (err) throw 'get ' + elem + ' ' + err;
                    console.log(elem + ' : A - attach');

                    db.query('SELECT CURRENT_DATE FROM RDB$DATABASE', [], function (err, result) {
                        if (err) throw 'query ' + elem + ' ' + err;
                        console.log(elem + ' : Q - query');

                        db.detach(function (err) {
                            if (err) throw 'detach ' + elem + ' ' + err;
                            console.log(elem + ' : D - detach ');
                        });
                    });
                });
            });

Output: (It changes each time)

--------------
 Pool created
--------------
0 : A - attach
1 : A - attach
4 : A - attach
5 : A - attach
6 : A - attach
7 : A - attach
8 : A - attach
2024-08-26T13:59:09.819Z - :[error]: uncaughtException: get 2 Error: Your user name and password are not defined. Ask your database
administrator to set up a Firebird login

  No stack trace
2024-08-26T13:59:09.820Z - :[error]: uncaughtException: get 3 Error: Your user name and password are not defined. Ask your database
administrator to set up a Firebird login

  No stack trace
9 : A - attach
0 : Q - query
0 : D - detach
1 : Q - query
1 : D - detach
4 : Q - query
4 : D - detach
5 : Q - query
5 : D - detach
6 : Q - query
6 : D - detach
7 : Q - query
7 : D - detach
8 : Q - query
8 : D - detach
9 : Q - query
9 : D - detach

[pwypustek]

Have you tried to call dbPool.get with async/await? Maybe these calls cannot be parallelized

[fabiobotsilva]

Have you tried to call dbPool.get with async/await? Maybe these calls cannot be parallelized

Yes, I've tried in many different ways.

With async/await, creating an async function:

const runQuery = async (dbPool: firebird.ConnectionPool, elem: number) => {
    dbPool.get(function(err, db) {      
        if (err) throw 'get ' + elem + ' ' + err;
        console.log(elem + ' : A - attach');

        db.query('SELECT CURRENT_DATE FROM RDB$DATABASE', [], function (err, result) {
            if (err) throw 'query ' + elem + ' ' + err;
            console.log(elem + ' : Q - query');

            db.detach(function (err) {
                if (err) throw 'detach ' + elem + ' ' + err;
                console.log(elem + ' : D - detach ');
            });
        });
    });
}

And then calling with await:

            [0,1,2,3,4,5,6,7,8,9].forEach(async (elem: number) => {
                await runQuery(dbPool, elem);
            });

I got the same error.

Putting all operations in separared async functions using promises, like that:

// Shared database connection pool
const dbPool = Firebird.pool(20, options);

// Get connection
const getConnection = async (): Promise<Database> => {
    return new Promise((resolve, reject) => {
        dbPool.get(async (err, connection) => { 
            if (err) reject(err); 
            resolve(connection);
        });
    });
};

// Get transaction
const getTransaction = async (connection: Database, isolation: string): Promise<Transaction> => {
    return new Promise((resolve, reject) => {
        const trnIsolation: Isolation = (isolation.toUpperCase() === 'REPEATABLE_READ') ? ISOLATION_REPEATABLE_READ : ISOLATION_READ_COMMITTED;
        connection.transaction(trnIsolation, async (err, transaction) => {
            if (err) {
                reject(err);
            };
            resolve(transaction);
        });
    });
};

// Get query result (inside transaction)
const getQuery = async (connection: Database, transaction: Transaction, sqltxt: string, params: string[]): Promise<any> => {
    return new Promise((resolve, reject) => {
        transaction.query(sqltxt, params, async (err, result) => {
            if (err) {
                transaction.rollback();
                reject(err);
            };
            transaction.commit();
            resolve(result);
        });
    });
};

// Execute query
export const runQuery = async (sqltxt: string, params: string[], isolation: string = 'READ_COMMITTED') => {
    return new Promise((resolve, reject) => {
        getConnection().then(
            (connection) => {
                getTransaction(connection, isolation).then(
                    (transaction) => {
                        getQuery(connection, transaction, sqltxt, params).then(
                            (result) => {
                                connection.detach();
                                resolve(result);
                            },
                            (error) => {
                                connection.detach();
                                reject(error);
                            }
                        );                                             
                    },
                    (error) => {
                        connection.detach();
                        reject(error);
                    }
                );
            },
            (error) => {
                reject(error);
            }
        );
    });
};

And calling runQuery inside forEach loop, sometimes get the same error.

I tried to use the classic attachment mode (attach instead pool.get) and the error persist, then I really think the problem is creating the connections and not the connections pool management. I've already tried to review the node-firebird sources, but I don't have enough knowledge about Firebird connection protocols.

I've been having this problem for a long time, but it's bothering me. Off course, we can always check the results and try sending the request again when it fails, but then we'll dealing with the consequences and not the problem source.

If you know of any other approach or suggestion that could be used, let me know and let's try it.

[pwypustek]

have you tried process.setMaxListeners(0) ?

[pwypustek]

Perhaps it is related to this issue: https://www.firebirdfaq.org/faq161/

[fabiobotsilva]

have you tried process.setMaxListeners(0) ?

I put this line at the start of file:

process.setMaxListeners(0);

But unfortunatelly the result was the same.

[fabiobotsilva]

Perhaps it is related to this issue: https://www.firebirdfaq.org/faq161/

This article treats of Firebird's Classic architecture and I am using only the SuperServer architecture. I have already tested with versions 3.0.10, 4.04 and 5.01 (local and remote), getting the same results with all of them. But I noticed an error that appears in firebird.log, every time the error occurs in the application an error is recorded "INET/inet_error: read errno = 10054, client host = ..."

This error can occur due to connections not closing correctly. I'm wondering if it's not a problem with opening/closing connections in the node-firebird. (only on high-load requests).

[pwypustek]

And in nodejs how do the debug logs look like, export NODE_DEBUG= or NODE_ENV=development NODE_DEBUG= node app.js

[fabiobotsilva]

After reviewing node-firebird I noticed that when the error occurs, the node-firebird (at line 466 in connection.js) was trying to fallback authentication to Legacy_Auth mode.

               if (!cnx.options.pluginName) {
                    if (cnx.accept.pluginName === pluginName) {
                        // Erreur plugin not able to connect
                        return cb(new Error("Unable to connect with plugin " + cnx.accept.pluginName));
                    }

                    if (pluginName === Const.AUTH_PLUGIN_LEGACY) { // Fallback to LegacyAuth
                        cnx.accept.pluginName = pluginName;
                        cnx.accept.authData = crypt.crypt(cnx.options.password, Const.LEGACY_AUTH_SALT).substring(2);

                        cnx.sendOpContAuth(
                            cnx.accept.authData,
                            Const.DEFAULT_ENCODING,
                            pluginName
                        );

                        return {error: new Error('login')};
                    }
                }

Looking at the node-firebird documentation, for Firebird 4 and above, it recommends using "AuthServer = Srp256, Srp, Legacy_Auth" in firebird.conf, and I was using it exactly like that.

So I tried reversing the order to "AuthServer = Legacy_Auth, Srp, Srp256" and (to my surprise), everything seems to work fine now, no more aleatory authentication errors.