Debugging Stored Procedures in MySQL

I have recently seen a couple of posts on the MySQL forums with regard to debugging stored procedures in MySQL. The people asking have been Oracle developers who like most people developing stored procedures in Oracle have been using an Oracle built in package called DBMS_OUTPUT. The package essentially accepts text which is then inserted into a buffer which can be viewed after a procedure has been executed (or even during using the correct tools). It got me thinking how easy it would be to add a similar debug method to MySQL so I came up with the following.

I created a new database called debug, this isn't strictly necessary but I like the idea of having a set area for the debug constructs to reside. I then created a table to hold the output, to keep it simple I simply gave it an id column so we could use the debug across a number of procedures at once, a text column to hold out debug statements and an auto increment column so I could order the results with some certainty.

DROP TABLE IF EXISTS debug.debug;
CREATE TABLE  debug.debug (
  id varchar(100) default NULL,
  debug_output text,
  line_id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (line_id)
)

I then created three procedures, debug_on to turn logging on, debug_insert to insert debugging messages and then finally debug_off to stop debugging, display the results and then clear the debug table of records for that id.

DROP PROCEDURE IF EXISTS `debug`.`debug_on` $$
CREATE PROCEDURE `debug_on`(in p_proc_id varchar(100))
begin
  call debug.debug_insert(p_proc_id,concat('Debug Started :',now()));
end $$

CREATE PROCEDURE `debug_insert`(in p_proc_id varchar(100),in p_debug_info text)
begin
  insert into debug (proc_id,debug_output)
  values (p_proc_id,p_debug_info);
end $$

CREATE PROCEDURE `debug_off`(in p_proc_id varchar(100))
begin
  call debug.debug_insert(p_proc_id,concat('Debug Ended :',now()));
  select debug_output from debug where proc_id = p_proc_id order by line_id;
  delete from debug where proc_id = p_proc_id;
end $$

I can now call these from my stored procedures to get debug information like so.

CREATE PROCEDURE test.test_debug()
begin
declare l_proc_id varchar(100) default 'test_debug';
  call debug.debug_on(l_proc_id);
  call debug.debug_insert(l_proc_id,'Testing Debug');
  call debug.debug_off(l_proc_id);
end $$

mysql> call test.test_debug();
+------------------------------------+
| debug_output                       |
+------------------------------------+
| Debug Started :2006-03-24 16:10:33 |
| Testing Debug                      |
| Debug Ended :2006-03-24 16:10:33   |
+------------------------------------+
3 rows in set (0.20 sec)

Query OK, 3 rows affected (0.23 sec)

Not rocket science but I'm sure all you Oracle users will be happy, you could of course call the DB DBMS_OUTPUT to make you feel really at home :).