The length result sometimes returns "0" when there is always at least one

[Madriix]

Hi

Sometimes there is a length == 0 error when there really is a result already. This therefore creates a duplicate by mistake. How to settle? Here is my code:

try {
    const [results] = await query("SELECT id FROM contacts WHERE account=:account ... LIMIT 1", {account: post.account, ...});
    if ( results.length == 0 ) { // From time to time the length returns "0" when there really is a result, it falsifies the results
        await query("INSERT INTO contacts SET ....", sql);
        return h.response("success").header('Cache-Control', 'no-cache, no-store, must-revalidate');
    }
    else if ( results.length == 1) {
        //...
        await query("UPDATE contacts SET .....", sql);
        return h.response("success").header('Cache-Control', 'no-cache, no-store, must-revalidate');
    }
    else
    return h.response("error").header('Cache-Control', 'no-cache, no-store, must-revalidate');


} catch (error) {
    return h.response("error").header('Cache-Control', 'no-cache, no-store, must-revalidate');
}

The first if if ( results.length == 0 ) is really executed from time to time when in reality there is a result in the sql database, which creates a duplicate or duplicates in sql, normally it should update with the "query update"

Where could the problem come from?

Cordially

[sidorares]

could that be a race condition? E.g this branch runs before expected record exists in a page ( later when you check manually it is there )

[Madriix]

yes, that's definitely it. do you have an idea to solve this?

[sidorares]

Unfortunately the answer is very generic:

• double check control flow is correct on the node side
• depending on the flow of data and execution you might need table / row level locks at a database level

Since the issue is likely outside of the scope of the driver and is a generic mysql server question I'm moving it to github discussions