MySQL operation failed: Unknown prepared statement handler (${#}) given to mysqld_stmt_execute

[hellopat]

I have a service that has been running in production for a month with no issues. Last evening, the service failed after all queries using .execute were throwing this error. Restarting the service resolved the issue.

Let me know if I can provide more information.

[sidorares]

interesting, never seen anything like this.

"prepared statement handler" is just integer, it's local to connection and usually starts with 1. When you do connection.execute('select 1+?', [1]) for the first time this is what happens:

client: prepare "select 1 + ?"
server: statement id = 1, expect one parameter etc etc
client: execute id=1, param = 1
server: ok, 1 column, 1 row = "2" 

When later you do connection.execute('select 1+?', [123]) same happens but instead of prepare id is looked in prepared statement cache

client, after realising there is already ps id for 'select 1+?': execute id=1, param = 1 
server: ok, 1 column, 1 row = "124" 

if you think you can possibly reproduce this, can you add console.log(this.statement.id) somewhere here https://github.com/sidorares/node-mysql2/blob/224c74de8184f78c540f343c432c0f37986c8aea/lib/commands/execute.js#L42 ?

This might indicate there is a bug in a way we cache statement id, but could be something else

[hellopat]

Thanks for the info @sidorares.

I found it strange myself that this occurred, especially after the service was running fine at load for a month.

I'll see if I can get something into place to debug if the issue occurs again.

[dekyfin]

I have been getting the same error on my app. The error occurs when script is called multiple times in a short period of time. I think module is using a cached statement even though the statement was closed. I would appreciate it if I could get a work-around. Will it be more appropriate to not close the statement?

This is a simplified version of the code

// A list of rows to be inserted into table
var data = [
    {col1: "val1", col2: "val2"},
    {col1: "val3", col2: "val4"}
];

var stmnt, conn; // Statement and connection variables to be used in this function

// Get a connection from Pool
Pool.getConnection( function( errCon, connection ){

    // Interupt the script if error occurs
    if( errCon ){
        finalize( errCon );
        return;
    }
    conn = connection; 

    // Prepare statement
    connection.prepare(
        "INSERT into table( col1, col2 ) VALUES ( ?, ? ) ",
        function( err, statement ){

            // Interupt the script if error occurs
            if( err ){
                finalize( err );
                return;
            }

            stmnt = statement;

            // Insert data into DB table
            data.forEach( row, index ){
                statement.execute([ row.col1, row.col2 ], (errIn, result) => finalize( errIn, index ) )
            }
            
        }
    )
}),

function finalize( err, index ){
    if( err instanceof Error){
        stmnt && stmnt.close();	// Close the prepared statement if it was set
        conn && conn.release(); // Release the connection if it was created

        console.log( err );
        res.send("an error occured");
        return;
    }

    if( data.length === index + 1 ){

        stmnt && stmnt.close();	// Close the prepared statement if it was set
        conn && conn.release(); // Release the connection if it was created

        res.send("success")
    }
}

[sidorares]

@dekyfin are you getting errors with your code? With manual .prepare() statement is not cached, it's up to you to reuse it or not

[rallfo]

@dekyfin are you getting errors with your code? With manual .prepare() statement is not cached, it's up to you to reuse it or not

Actually it does appear that manual prepare() statements are both cached and retrieved from the connection._statements LRU cache. Which is the source of the bug.

In the case of a manual .prepare() then PreparedStatementInfo.close() a CloseStatement is sent but the statement entry is not removed from the cache. Leaving an entry to the invalid prepared statement.

Replication: prepare() an insert query, execute() and close() it. Attempting to prepare() and execute() the same SQL a second time triggers this issue.

Solution:

• either stop caching manual prepare() statements
• or make PreparedStatementInfo.close() correctly delete the statement from the connection._statements cache

[lionsoftware]

Because the issue not yet resolved, I found only one way to workaround: use connection.unprepare() My code with async/await looks like this:

    async someFunction(some_xxx_list) {
        let conn, sql1, stmt1, stmt1promise; // define variables
        try {
            conn = await this.pool.getConnection(); // get connection from pool
            sql1 = "SELECT xxx FROM yyy WHERE zzz = ?"; // save SQL for future use
            stmt1promise = conn.prepare(sql1); // get Promise for statement
            const result = [];
            for (const val of some_xxx_list) {
                stmt1 = await stmt1promise; // get statement
                const [row] = await stmt1.execute([val[0]]); // use statement
                if (Array.isArray(row) && row.length === 1) { // my ugly stuff, you may skip it
                    result.push(val);
                }
            }
            return result;
        } finally {
            if (stmt1) await conn.unprepare(sql1); // Very important thing: unprepare previous statement by SQL text.
            if (conn) await conn.release(); // return connection back to the pool
        }
    }

[xWTF]

TL;DR for anyone facing the same issue: rallfo found the exact cause of this, simply use Connection.unprepare() when you've done with your instead of PreparedStatementInfo.close() solves the problem. I’ve opened https://github.com/sidorares/node-mysql2/pull/1493 to address this, hopefully it clarifies the usage.