NodeJS Promise returns undefined value under random circumstances

Question

First of all, I'm sorry if this question already has an answer, but I've searched for this kind of question and found nothing useful.

I'm developing a NodeJS application that uses the mysql npm package to query a database. I have written several queries which are working fine with the help of promises (because that's how the SQL module works). I have no problem when query procedure function get_works (defined below) is called normally. However, when I call this procedural function in Express Router, I get strange behavior.

view.get("/works", (req, res) => {
    (async () => {
        res.json(await get_works());
    })()
});

async function get_works(offset=0,limit=6){

    let cdc_database = mysql.createConnection({
        /*Filled with credentials*/
    });
    
    const get_texts = `SELECT TEXID,TXNOM,TXRES FROM TEXTE LIMIT ${limit} OFFSET ${offset};`; // Valid request

    /*Two steps: get texts and get authors of each texts*/

    cdc_database.connect();
    const texts = await database_promise_query(cdc_database, get_texts);
    /*This await always have a correct output*/

    let text_authors = [];

    for(i = 0; i < texts.length; i++){

        get_text_authors = `SELECT AUPRE, AUNOM FROM AUTEURS,ECRIT_PAR\
        WHERE AUTEURS.AUTID=ECRIT_PAR.AUTID AND TEXID=${texts[i].TEXID};`; // Request is valid

        text_authors[i] = (await database_promise_query(cdc_database, get_text_authors));
        /*          ^^^^^^^^
        The await above returns an undefined value randomly when called through the router
        */
        if(text_authors[i] === undefined){
            console.error(`[get_works] - Server error on ${i}th text's authors`)
            text_authors[i] = [{AUPRE: "Prénom", AUNOM: "Nom"}]
        }

    }
    

    cdc_database.end();

    return works_to_JSON(texts, text_authors)
}

Sometimes, a query to the database will have no undefined values, but once there is one undefined value, all subsequent values ​​will also be undefined.

Example trace log

(1st call, 3 errors)
[
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: 'xxx' } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: "xxx" } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: 'xxx' } ],
  [ { AUPRE: 'Prénom', AUNOM: 'Nom' } ],
  [ { AUPRE: 'Prénom', AUNOM: 'Nom' } ],
  [ { AUPRE: 'Prénom', AUNOM: 'Nom' } ]
]

(2nd call, no error)
[
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: 'xxx' } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: "xxx" } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: 'xxx' } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: 'xxx' } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: "xxx" } ],
  [ RowDataPacket { AUPRE: 'xxx', AUNOM: 'xxx' } ],
]

This is my promise wrapper:

function database_promise_query(database, query){
    return new Promise(res => {
        database.query(query, (error, results, fields) => {
            if(error) {res(undefined); console.log(error)}
            res(results);
        })
    })
}

However, calling this function outside of the router like this works perfectly:

(async () => {
    await get_works(0, 10);
    await get_works(10, 10);
    await get_works(20, 10);
    await get_works(30, 10);
})()

Did I miss something? I think all the queries are correct as I can get the desired results, the problem may be with my async/await processing. Thanks for all the help you can provide.

Edit 1

According to @tromgy's suggestion, I rewrote the promise wrapper.

function database_promise_query(database, query){
    return new Promise((res, rej) => {
        database.query(query, (error, results, fields) => {
            if(error) {rej(error); throw new Error(error)}
            console.log("Received response", results)
            res(results);
        })
    })
}

However, the rejection never occurs (which is reasonable since there is no matching data in the database). To clean up the logs, I disabled another router for similar requests using a different database connection. Removing this router also resolved the bug. I don't know how this is possible (maybe a variable is declared global, so I would look for that kind of problem).

Answer 1

As shown in the figure, the problem is a shared loop counter. One of my colleagues forgot to add the let keyword before iterating, so the for loop jumped the values.

I hate this JavaScript feature right now. Thanks for the help!