Calling it a day.

I have been blogging since last June (is it really that long) and www.mysqldevelopment.com has been going for over a year now. But as the frequency of posts on this blog and updates on the site will testify recently I just haven't had the time I used to have with regards to MySQL. One of the good and bad things with the web is that it's relatively cheap to just leave stuff hanging around where in traditional media, books for example, the costs of continual production mean things go out of print never to be seen again. This means that the web is full of sites and pages which are left untouched for months or years. Most of the time that isn't a problem but often, especially with IT related sites the information can be way off the mark.

It's with that in mind that as from 1st May 2006 www.mysqldevelopment will no longer exist and this will be my last MySQL related blog entry. I no longer have the time or energy to commit to it and with out that the site won't move forward. Of course if somebody wanted to take it on that would be great but privately I have been looking at ways to move the site forward without success.

I want to say thanks to all the people who took the time to read my blog and to visit MySQLDevelopment.com and definately a huge thank you to those who contributed to the site over the last year.

So long and thanks for all the fish.

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

Do you really want to comment out that code?

Yet again I have been on the MySQL forums and yet again it's given me something to write about. The question was pretty standard simply asking how you comment out code or add comments to MySQL stored routines. However the wording of the question got me thinking. In the past when I have been writing complex stored procedures in Oracle it can be difficult to see where an error is coming from, not necessarily which line is raising the error but which section of code cause the problem. One of the methods I have used in the past is to use the /* */ multi line comment syntax to exclude blocks of code on mass to rule them out quickly, this has proved a good way to narrow down where the root of an error comes from.

But as you may know MySQL simply removes any comments from the code if it's entered via the command line, it makes no distinction between comments you are adding and code which has been commented out. This isn't a problem when you write procedures in script files and upload them but if you use the command line directly (either typing them in or copy and paste) it means the commented code will be lost forever.

Yet another example of why you don't want to write stored procedures directly into the command line.

Event Scheduler on Mac OS X

One of the things I learnt a year ago when I first started working with MySQL stored procedures was that you really need to have the latest version of MySQL running. By the time you have identified a possible bug and noted down the specifics a new version was available which invariably fixed the problem.

This is also true with release 5.1, I have recently started writing content on the event scheduler over at www.mysqldevelopment.com and found a few bugs early on. Given that 5.1 is still very much in development I tend not to raise bugs these days without first checking on the forums and then making sure I have the very latest release to test against.

One of the biggest issues I had was that one off events worked fine, but events that fired at intervals didn't. I say they didn't but one of the big problem with testing events is that you have to wait a long time to prove something. I was using 1 minute intervals which didn't work, I also tried 5 and 10 minute intervals also. Testing hour intervals was just about practical but any more than that was just too time consuming. I checked on the forum but there doesn't seem to be a great deal of activity in the event scheduler section at present, so I downloaded a newer version of 5.1 (in this case going from 5.1.6 to 5.1.7), testing again proved positive and it seems whatever the problem had been was now fixed.

I think the event scheduler is a great new feature and I can't wait to get the content finished, unfortunately it's taking a bit longer than I hoped due to other commitments and also the fact it takes a fair amount of time to testing things.

Spread the word

As you may or may not know I'm a sysop over at the Quest Pipelines MySQL forum. We don't get a great deal of visitors in the MySQL forums there, due in part I guess to the fact the forums on the MySQL website are so good.

However in addition to running the forums Quest produce a monthly database newsletter which is sent to over 28,000 database professionals. Subjects vary but the general format is an article on Oracle, SQL Server, DB2 and for the last year or so MySQL. For me this is a great way to spread the MySQL word to other database users but unfortunately I'm insanely busy with the day job so I can't submit anything this month.

So if you have something written or would like to contribute then let the newsletter team know at

newsletter@quest-pipelines.com

Or have a look at the latest addition over at the MySQL area of the pipelines site.

http://www.quest-pipelines.com/pipelines/mysql/index.asp