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 8:56 PM, Blogger Debt Help said...

Get your free no hassle debt consolidation quote today Student loan consolidation home loans, Mortgage refinancing, and Home equity line of credit
Lower your monthly payments and get out of debt in less than half the timePay less monthly and get rid off all your high interest bills today, get your free quote and regain control over your finances.

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

Thanks, it was very useful for me.

Keep sharing !!

God bless you

At 9:02 AM, Blogger dhenz dheanz said...

What i don't understood is actually how you're not really much more well-liked than you may be right now. You're very intelligent. You realize therefore significantly relating to this subject, made me personally consider it from numerous varied angles. check over here
Its like men and women aren't fascinated unless it?s one thing to accomplish with Lady gaga! Your own stuffs excellent. Always maintain it up! this content

At 8:04 AM, Anonymous Anonymous said...

Helpful info. basics Lucky me I discovered your website by chance, article source and I am shocked why this coincidence did not came about earlier! I bookmarked it. article

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.


Post a Comment

<< Home