0

I was handed down a complex MySQL stored procedures that has TRANSACTION and tasked to find out an issue. I've followed several answers here in stackoverflow on "debugging a stored procedure" and produces my own process:

Log table:

CREATE TABLE `dt_sp_process_logs` (
`serial` varchar(20) NOT NULL,
`params` TEXT,
`message` TEXT NOT NULL,
`createdAt` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SP for logging:

DELIMITER %% CREATE PROCEDURE `spWriteLog`(
  IN `serialCode` VARCHAR(20),
  IN `params` TEXT,
  IN `messageText` TEXT
)
BEGIN

    INSERT INTO dt_sp_process_logs(`serial`, `params`, `message`)
    VALUES(serialCode, params, messageText);

END %%

I've called the SP inside my to-be-debugged SP:

BEGIN
  SET code = 0;

  SET serialN = CONCAT(
    'OR',
    REPLACE(REPLACE(REPLACE(CURRENT_TIMESTAMP, ':', ''), '-', ''), ' ', ''),
      (CAST(RAND() * 60 AS UNSIGNED) + 1)
  );

  START TRANSACTION

  -- -------
  -- long and
  -- complex process
  -- that determine
  -- code value
  -- -------

  CALL spWriteLog(serialN, "", CONCAT("subTotalItems: ", subTotalItems, " || subTotal: ", subTotal));

  -- -------
  -- long and
  -- complex process
  -- that determine
  -- code value
  -- -------

  IF code = 1 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;

END

But it doesn't gets recorded in my dt_sp_process_logs. My immediate conclusion is that because this SP is called inside transaction, it doesn't gets saved when the to-be-debugged procedure called for "rollback". Is there any other technique I can learn?

ariefbayu
  • 21,849
  • 12
  • 71
  • 92
  • 1
    I see not transaction or rolllback. – P.Salmon Feb 21 '21 at 14:56
  • @P.Salmon sorry, I included the transaction and rollback process – ariefbayu Feb 21 '21 at 15:04
  • You need to expose a bit more of your code as is you get an unknown column error on serialN – P.Salmon Feb 21 '21 at 15:12
  • updated with serialN initialization. – ariefbayu Feb 21 '21 at 15:16
  • 2
    If you rollback your transaction, that includes changes you made to the log tables inside that transaction (as that is what a transaction is supposed to do). A workaround is to use MyISAM tables for logging, which do not support transactions, and thus won't get rolled back. – Solarflare Feb 21 '21 at 15:26
  • thank you @Solarflare, please move your comment as an answer and I'll vote as accepted! – ariefbayu Feb 21 '21 at 15:29
  • 1
    hm usually you set up a couple of user defined variables and enter there the viltal debugging information, and after running the stored procedure you select them and see if you can grasp the problem, or do you need more information – nbk Feb 21 '21 at 17:53

0 Answers0