How do you debug MySQL stored procedures?

Question

My current process for debugging stored procedures is very simple. I create a table called "debug" where I insert variable values from the stored procedure as it runs. This allows me to see the value of any variable at a given point in the script, but is there a better way to debug MySQL stored procedures?

Solution 1

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.

Solution 2

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

Solution 3

How to debug a MySQL stored procedure.

Poor mans debugger:

1. Create a table called logtable with two columns, id INT and log VARCHAR(255).

2. Make the id column autoincrement.

3. Use this procedure:

   delimiter //
   DROP PROCEDURE `log_msg`//
   CREATE PROCEDURE `log_msg`(msg VARCHAR(255))
   BEGIN
       insert into logtable select 0, msg;
   END
   

4. Put this code anywhere you want to log a message to the table.

   call log_msg(concat('myvar is: ', myvar, ' and myvar2 is: ', myvar2));
   

It's a nice quick and dirty little logger to figure out what is going on.

Solution 4

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

Solution 5

There are GUI tools for debugging stored procedures / functions and scripts in MySQL. A decent tool that dbForge Studio for MySQL, has rich functionality and stability.

View more solutions

Comments

[Cory House]

I've used Toad for years but wasn't aware it had any special features for debugging sprocs. Can you clarify how you use Toad to do so?

[Joyce]

Looked at Toad 6.3 for mysql just now, looks like there is debug feature with breakpoints and everything. Do you mean that the debug feature is not working? Or maybe your version is older & doesn't include debug feature?

[kellogs]

i was so eager to try it out. Unfortunately it is a total wreckage. I get "function coalesce does not exist" error message suppsedly from mysql, as a result the GUI branches incorrectly through SP code (although MySQL runs it correctly). Not to mention the "DECLARE var DEFAULT value" local variables. They just show up as NULL when they clearly are not. Oh, and also "Undeclared identifier: 'FETCH_RADIUS_DISTSORT'" where that was a compiled statement. Not recommended.

[Anup]

Not all platforms @Bob Probst , sybase debugging tools are quiet decent with breakpoint debug for trigger and stored procedures

[Patrick M]

This doesn't seem to work for FUNCTIONS and I have no idea why. It always gives "Error Code: 1415. Not allowed to return a result set from a function". Is there any recourse?

[Guy]

Difficult to find what platforms that debug tool runs on. Seems to run on Windows. Anything else?

[Steve Chambers]

It's not perfect but my trial with this has been a very different experience to that reported by @kellogs above. The tool is nice and lightweight and seems to do just the job needed without any bloat. It was a far better experience for me than any of the other tools trialled (i.e. Visual Studio, Toad and dbForge Studio, all of which had major flaws - would describe all of these as a "total wreckage" in comparison). Not sure whether this is because the function being debugged didn't include any of the faulty constructs or whether the issues have been fixed.

[ralfe]

I also found this tool to be quite useful for debugging my stored procedures.

[Xenos]

@PatrickM Functions cannot return rows ("result") while this debug procedure relies on it (the debug messages are resultsets returned in the procedure call). In functions, you may only INSERT INTO my_log_table (message) VALUES (msg) and maybe retrieve all debug messages once function calls are over (ie: you're back in the procedure)

[Hooman Bahreini]

There is an issue with multi-host connection string when using MySQL and Connector .NET. I have explained the issue here.. .I was wondering if anyone is going to look into this? This has caused quite a bit of problem for many of us .Net developers who use MySQL...

[Fernando Gonzalez Sanchez]

Sorry to hear that, I no longer work at Oracle, and have no a lot of free time, I suggest getting in touch with MySQL support.

[mustafa kemal tuna]

This aproach is good but writing to console isn't effective on MySQL Workbench like IDEs. because every "select" statement opens new result pane. I think it is better to create a temporary log table to log error messages with time stamp and procedure name

[user1710989]

Yes I do exactly the same. I place select statement and check the status of the tables and related values. I sometime use temporary table to insert values in tables and once the solution is found, I remove temporary tables and those select statements.

[Smruti R Tripathy]

I couldn't find a way to use it for trigger. Not sure there is a way using it.