How do you debug MySQL stored procedures?

The following debug_msg procedure can be called to simply output a debug message to the console:

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    select concat('** ', msg) AS '** DEBUG:';  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 always shows up');  call debug_msg(FALSE, 'This message will never show up');END $$DELIMITER ;

Then run the test like this:

CALL test_procedure(1,2)

It will result in the following output:

** DEBUG:** my first debug message** DEBUG:** arg1:1** DEBUG:** This message always shows up

I do something very similar to you.

I'll usually include a DEBUG param that defaults to false and I can set to true at run time. Then wrap the debug statements into an "If DEBUG" block.

I also use a logging table with many of my jobs so that I can review processes and timing. My Debug code gets output there as well. I include the calling param name, a brief description, row counts affected (if appropriate), a comments field and a time stamp.

Good debugging tools is one of the sad failings of all SQL platforms.

Yes, there is a specialized tools for this kind of thing - MySQL Debugger.