Uploaded image for project: 'MariaDB Connector/node.js'
  1. MariaDB Connector/node.js
  2. CONJS-240

Repeating calling the same procedure gets a release prepare error.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • 3.1.1
    • execute
    • None
    • node.js connector 3.02
      mariadb 10.6.7
      ubuntu linux 20.04

    Description

      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

      { name: "Database Error:", message: dbResponseErrorMsg, }

      ;
      }

      // 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 ;

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            1222tmiller Tom Miller
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.