SQLState: HY000) Unknown prepared statement handler (32) given to mysqld_stmt_execute
You can find info here: https://github.com/sidorares/node-mysql2/issues/805
We are getting close to going live and I can't find a workaround.
The main difference here is I am not doing a manual prepare, but it sounds like the same problem of releasing the prepare object but not cleaning up a pointer list. This stored proc runs every 1m 30s. This runs fine for about an hour and then I start getting the error. There is a TRANSACTION / COMMIT in the stored procedure, so it isn't leaving something open.
==========================================================
mdbPool = getPool();
mdbConn = await mdbPool.getConnection();
// Use a stored procedure to select and set a job number to prevent duplicate sending of emails.
const resultSpPrepare = await mdbConn.execute("CALL
M_Email_Prepare_To_Send_U_sp
;", [
data.RequestUserId,
]);
// Check for db error
if (JSON.stringify(resultSpPrepare).includes("@full_error")) {
const dbResponseErrorMsg = JSON.stringify(resultSpPrepare[0][0]["@full_error"]);
dlog("Throw", dbResponseErrorMsg);
throw
;
}
// No error, get returned ID to process emails
const jobNumber = resultSpPrepare[0][0].JobNumber;
=====================================================
DELIMITER //
CREATE OR REPLACE PROCEDURE EM_Email_Prepare_To_Send_U_sp(IN pin_RequestUserId BigInt(20))
NO SQL
SQL SECURITY INVOKER
BEGIN
DECLARE aTimeStamp DateTime DEFAULT UTC_TIMESTAMP(3);
DECLARE aJobNumber BigInt(20);
DECLARE aDbId nVarChar(50);
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
ROLLBACK;
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;
SELECT KeyValue INTO aDbId FROM EM_Config WHERE SubSys = 'CORE' AND KeyName = 'coreDatabaseId';
SET aJobNumber = NEXT VALUE FOR EM_EmailSeq;
START TRANSACTION;
UPDATE EM_Email SET
emProcJobNumber = aJobNumber,
emProcUTCTimeStamp = atimeStamp,
UpdateUserId = pin_RequestUserId,
UpdateUTCTimeStamp = aTimeStamp
WHERE ((emProcStatus = 'Queued') AND (emProcJobNumber = 0)) or
((emProcStatus = 'Retry') AND (emProcJobNumber = 0) AND
(DATE_ADD(emProcUTCTimeStamp, INTERVAL (POWER(emProcTryCount, 2.5)) HOUR) < NOW()));
COMMIT;
SELECT aJobNumber AS JobNumber;
END //
DELIMITER ;