paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL, how to debug a trigger or procedure code

There are several ways to debug a MySQL code (triggers and procedures) and some of these are tools (free or not) that are so complete and efficient, as MySQL Debugger, dbForge or Toad Mysql, but if you need to develop a quick and simple debug, this is my suggestion.

First of all, we have to distinguish between procedure and trigger. In fact, in case of the trigger, we have some restriction that not allow, for example, showing the prompt message, etc... (We can find an interesting and exhaustive posts in stackoverflow forum)

Procedure
We could use this simple code (thanks to Brad Parks and his stackoverflow post), where we'll create a procedure calling debug_msg and we'll call it into the others functions. This is the code

DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_msg`$$
DROP PROCEDURE IF EXISTS `test_procedure`$$

CREATE PROCEDURE debug_msg(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN BEGIN
    select concat("** ", msg) AS '** DEBUG:';
  END; END IF;
END $$

CREATE PROCEDURE test_procedure(arg1 INTEGER, arg2 INTEGER)
BEGIN
  SET @enabled = TRUE;

  call debug_msg(@enabled, "my first debug message");
  call debug_msg(@enabled, (select concat_ws('',"arg1:", arg1)));
  call debug_msg(TRUE, "This message will show up no matter what the value of enabled is");
  call debug_msg(FALSE, "This message will never show up");
END $$

DELIMITER ;

CALL test_procedure(1,2)


Trigger
In this case we'll create a simple table called debug

CREATE TABLE IF NOT EXISTS `debug` (
  `iddebug` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `message` varchar(256) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`iddebug`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

then into the trigger code, we'll add this code:
- Declare a boolean variable to decide if debug will run

DECLARE do_debug BOOLEAN DEFAULT TRUE;

- Delete always the table debug

    IF do_debug THEN
	DELETE FROM debug;
    END IF;

- Adding our message, using the concat mysql function

...
	SET idcustomer       =  NEW.id_customer;

	IF do_debug THEN
	    	INSERT INTO debug (message, fecha)
      		       VALUES (CONCAT('ID CUSTOMER--> ',idcustomer),NOW());
        END IF;


That's all! ;)

Note: The triggers doesn't allow to execute the commit, so we cannot, for example, use the truncate command instead of delete, neither this sql command

ALTER TABLE debug AUTO_INCREMENT = 0;

The return error is

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.