How to log within a stored procedure that is encapsulated within a transaction

Question

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I actually want to do is to log if the execution of sp_B or sp_C fails and if so, which were the arguments passed to the failing stored procedure. But as sp_B and sp_C do some inserts in some tables, I need to do a XA ROLLBACK if sp_B or sp_C fails.

So my question is: How can I log into a table while I am within a transaction (or how to execute an insert statement outside of the transaction scope)?

To give you a little example of what I want to do (I've simplyfied the code and renamed most of the parameters/variables used to focus on the problem, I would never dream of using parameter names like a, b or c ;-) ):

The stored procedure sp_A looks like this:

CREATE PROCEDURE `sp_A`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  XA START 'sp_A';

  CALL sp_B(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -1;
    SET l_errorMessage = CONCAT('CALL sp_B(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  CALL sp_C(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -2;
    SET l_errorMessage = CONCAT('CALL sp_C(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  XA END 'sp_A';
  XA COMMIT 'sp_A';
  SET c = 1;
END;

The stored procedure sp_B looks like this (sp_C looks similar):

CREATE PROCEDURE `sp_B`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  INSERT INTO `someTable` (`a`, `b`) VALUES (a, b);

  IF l_errorOccured THEN
    SET c = -1;
    SET l_errorMessage = CONCAT('INSERT INTO `someTable` (`a`, `b`) VALUES (', a, ', ', b, ');');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  SET c = 1;
END;

sp_Log just inserts data into a table which I want to use as my logging table.

So actually the logging within sp_A works just fine (because the call to sp_Log is outside a transaction) but the logging within sp_B or sp_C won't work because by rolling back the transaction started in sp_A, the inserts done by the call of sp_Log within sp_B or sp_C are reverted of course.

(I have simplified the example, in sp_B and sp_C. There is more going on than just one simple insert statement and just to have more fun, there are transactions started and commited/rolled back within sp_B and sp_C as well.)

I would appreciate any hints you could give me. Thanks!

Answer 1

It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.