Nested Transaction and Fetch functions stops process

In the last time we encountered bigger issues with database connection or better said, our whole Node.js process stopped without any error message.

I found out the issue and will present it to you, it is definitely related to firebird but im not sure if its an issue of this library.

Error as mentioned: None.

This is our function:

const query = helper.getCountOrderItemsCancelledQuery(orderId);
fbhelper.dbTransactionFetch(this.connectionString, query, (err, rows) => {
    if (err) {
        return reject(err);
    }
    const query2 = helper.getCountOrderItemsQuery(orderId);
    fbhelper.dbTransactionFetch(this.connectionString, query2, (err, rows2) => {
        if (err) {
            return reject(err);
        }
        return resolve(true);
    });
});

This is our shared dbTransactionFetch:

export function dbTransactionFetch(connectionString: ConnectionString, query: string, callback: (err: Error, response: any[]) => void, maxResults?: number) {
    logger.debug("Accessing dbTransactionFetch with query: " + query);
    // Default number of rows to fetch is all rows, otherwise use maxResults parameter
    const rowCount: any = maxResults || "all";

    const connection = <fb.ConnectionModified>fb.createConnection();

    connectionMutex.Synchronize(() => {
        return new Promise<void>((resolve, reject) => {
            connection.connect(connectionString.Host, connectionString.UserID, connectionString.Password, "", (err) => {
                if (err) {
                    logger.error("Error in dbTransactionFetch: passed to callback");
                    reject(); // Returning mutex
                    return callback(err, null);
                }
                resolve(); // Returning mutex
                connection.startNewTransaction((err: Error, fbTransObj: fb.Transaction) => {
                    if (err) {
                        logger.error("Error in dbTransactionFetch: passed to callback");
                        if (connection.connected) {
                            connection.disconnect();
                        }
                        return callback(err, undefined);
                    }
                    fbTransObj.query(query, (err: Error, fbResult: fb.FBResult) => {
                        if (err) {
                            logger.error("Error in dbTransactionFetch: passed to callback");
                            fbTransObj.rollback((err) => {
                                if (err) {
                                    logger.error("Error in dbTransactionFetch: could not roll back transaction, connection left open", err);
                                } else {
                                    if (connection.connected) {
                                        connection.disconnect();
                                    }
                                }
                            });
                            return callback(err, undefined);
                        }
                        const rows: any = [];

                        // Do to unpredictable library behaviour the fbResult object is sometimes returned as an row object resulting in a critical
                        // Error when being used normally.
                        if (fbResult.fetch) {
                            logger.debug("Getting rows from fbResult in dbTransactionFetch, fbResult:", fbResult);
                            fbResult.fetch(rowCount, true, (row: any) => {
                                logger.silly("row begins");
                                logger.silly(row);
                                rows.push(row);
                                logger.silly("row ends");
                            }, (err: Error, eof: boolean) => {
                                if (err) {
                                    logger.error(err.toString());
                                    fbTransObj.rollback((err) => {
                                        if (err) {
                                            logger.error("Error in dbTransactionFetch: could not roll back transaction, connection left open", err);
                                        }
                                        if (connection.connected) {
                                            connection.disconnect();
                                        }
                                    });
                                    return callback(err, undefined);
                                }
                                logger.debug("File ended?: " + eof);
                                if (rows.length === 0) {
                                    logger.debug("response to database query was empty");
                                }
                                fbTransObj.commit((err: Error) => {
                                    if (err) {
                                        logger.error("Error in dbTransactionFetch: passed to callback");
                                        fbTransObj.rollback((err) => {
                                            if (err) {
                                                logger.error("Error in dbTransactionFetch: could not roll back transaction, connection left open", err);
                                            }
                                            if (connection.connected) {
                                                connection.disconnect();
                                            }
                                        });
                                        return callback(err, undefined);
                                    }
                                    logger.debug("Committing transaction in dbTransactionFetch and returning rows:", rows);
                                    if (connection.connected) {
                                        connection.disconnect();
                                    }
                                    return callback(err, rows);
                                });
                            });
                        } else {
                            fbTransObj.commit((err: Error) => {
                                if (err) {
                                    logger.error("Error in dbTransactionFetch: passed to callback");
                                    fbTransObj.rollback((err) => {
                                        if (err) {
                                            logger.error("Error in dbTransactionFetch: could not roll back transaction, connection left open", err);
                                        }
                                        if (connection.connected) {
                                            connection.disconnect();
                                        }
                                    });
                                    return callback(err, undefined);
                                }
                                logger.debug("Committing transaction in dbTransactionFetch and returning rows:", fbResult);
                                if (connection.connected) {
                                    connection.disconnect();
                                }
                                const returnArr: any[] = [];
                                returnArr.push(<Object>fbResult); // Typecast fbResult into generic object to satisfy return type
                                return callback(err, returnArr);
                            });
                        }
                    });
                });
            });
        });
    });
}

This is our shared dbQuery:

export function dbQuery(connectionString: ConnectionString, query: string, callback: (err: Error, response: any[]) => void, maxResults?: number) {
    logger.debug("Accessing dbQuery with query: " + query);
    // Default number of rows to fetch is all rows, otherwise use maxResults parameter
    const rowCount: any = maxResults || "all";

    const connection = <fb.ConnectionModified>fb.createConnection();

    connectionMutex.Synchronize(() => {
        return new Promise<void>((resolve, reject) => {
            connection.connect(connectionString.Host, connectionString.UserID, connectionString.Password, "", (err) => {
                if (err) {
                    logger.error("Error in dbQuery: passed to callback");
                    reject(); // Returning mutex
                    return callback(err, null);
                }
                resolve(); // Returning mutex
                connection.query(query, (err: Error, fbResult: any) => {
                    if (err) {
                        logger.error("Error in dbQuery: passed to callback");
                        if (connection.connected) {
                            connection.disconnect();
                        }
                        return callback(err, fbResult);
                    }
                    const rows: any = [];
                    logger.debug("Getting rows from fbResult in dbQuery");

                    fbResult.fetch(rowCount, true, (row: any) => {
                        logger.silly("row begins");
                        logger.silly(row);
                        rows.push(row);
                        logger.silly("row ends");
                    }, (err: Error, eof: boolean) => {
                        if (err) {
                            logger.error(err.toString());
                            if (connection.connected) {
                                connection.disconnect();
                            }
                            return callback(err, null);
                        }
                        logger.debug("File ended?: " + eof);
                        if (rows.length === 0) {
                            logger.debug("response to database query was empty");
                        }
                        if (connection.connected) {
                            connection.disconnect();
                        }
                        return callback(err, rows);
                    });
                });
            });
        });
    });
}

firebird.d.ts:

import "firebird";

declare module "firebird" {
    export class ConnectionModified extends Connection {
        disconnect(): void;
    }
}

Logs of firebird.log when this happened:

HOTSV005    Thu Oct 27 17:09:34 2022
    INET/inet_error: read errno = 10054, client host = hotsv005, address = 127.0.0.1/60195, user = administrator

If i switch to dbQuery instead of dbTransactionFetch it works fine, no error and the process is not stopping. Functionality wise dbTransactionFetch was not required, but worked anyways, here as we only did SELECT's but still i don't think it should break like this.

Interesting here as well, this did not cause to break the process everything but got worse when upgrading from Firebird 3.0.7 to Firebird 3.0.10.

Confirmed on host os: Windows 10, Windows 11, Windows Server 2019, Windows Server 2012 R2

xdenser / node-firebird-libfbclient

Nested Transaction and Fetch functions stops process #135