counter customizable free hit

Friday, March 24, 2006

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.

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))
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)
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))
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()
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 :).


At 6:59 PM, Blogger Unknown said...

Thanks, it was very useful for me.

Keep sharing !!

God bless you

At 9:03 AM, Blogger Gozone said...

Many thanks, you helped me a lot.

At 10:01 PM, Anonymous Anonymous said...

These robo comments are getting more and more intelligent. I wonder if the AI improved or it is just a better template. Maybe a better template results in better AI. Anyway they are 3.2x more annoying now.
But thanks for this post, I will use it.

At 11:23 PM, Blogger Melvinchi said...

I know you are doing a proof of concept and it's great! Change the stored procedure or your table. They are inconsistent.

The table column name is id but is referenced as proc_id in the debug_insert stored procedure.


Post a Comment

<< Home