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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 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.

Answer 6

Debugger for mysql was good but its not free. This is what i use now:

DELIMITER GO$

DROP PROCEDURE IF EXISTS resetLog

GO$

Create Procedure resetLog() 
BEGIN   
    create table if not exists log (ts timestamp default current_timestamp, msg varchar(2048)) engine = myisam; 
    truncate table log;
END; 

GO$

DROP PROCEDURE IF EXISTS doLog 

GO$

Create Procedure doLog(in logMsg nvarchar(2048))
BEGIN  
  insert into log (msg) values(logMsg);
END;

GO$

Usage in stored procedure:

call dolog(concat_ws(': ','@simple_term_taxonomy_id',  @simple_term_taxonomy_id));

usage of stored procedure:

call resetLog ();
call stored_proc();
select * from log;

Answer 7

Another way is presented here

http://gilfster.blogspot.co.at/2006/03/debugging-stored-procedures-in-mysql.html

with custom debug mySql procedures and logging tables.

You can also just place a simple select in your code and see if it is executed.

SELECT 'Message Text' AS `Title`; 

I got this idea from

http://forums.mysql.com/read.php?99,78155,78225#msg-78225

Also somebody created a template for custom debug procedures on GitHub.

See here

http://www.bluegecko.net/mysql/debugging-stored-procedures/ https://github.com/CaptTofu/Stored-procedure-debugging-routines

Was mentioned here

How to catch any exception in triggers and store procedures for mysql?

Answer 8

I'm late to the party, but brought more beer:

http://ocelot.ca/blog/blog/2015/03/02/the-ocelotgui-debugger/ and https://github.com/ocelot-inc/ocelotgui

I tried, and it seems pretty stable, supporting Breakpoints and Variable inspection.

It's not a complete suite (just 4,1 Mb) but helped me a lot!

How it works: It integrates with your mysql client (I'm using Ubuntu 14.04), and after you execute:

$install
$setup yourFunctionName

It installs a new database at your server, that control the debugging process. So:

$debug yourFunctionName('yourParameter')

will give you a chance to step by step walk your code, and "refreshing" your variables you can better view what is going on inside your code.

Important Tip: while debugging, maybe you will change (re-create the procedure). After a re-creation, execute: $exit and $setup before a new $debug

This is an alternative to "insert" and "log" methods. Your code remains free of additional "debug" instructions.

Screenshot:

Answer 9

I just simply place select statements in key areas of the stored procedure to check on current status of data sets, and then comment them out (--select...) or remove them before production.

Answer 10

MySQL Connector/Net 6.6 has a feature to Debug Stored Procedures and Functions

Installing the Debugger

To enable the stored procedure debugger:

• For Connector/Net 6.6: Install Connector/Net 6.6 and choose the Complete option.
• For Connector/Net 6.7 and later: Install the product MySQL for Visual Studio, to which the stored procedure debugger belongs.

Starting the Debugger

To start the debugger, follow these steps:

• Choose a connection in the Visual Studio Server Explorer.
• Expand the Stored Procedures folder. Only stored procedures can be debugged directly. To debug a user-defined function, create a stored
  procedure that calls the function.
• Click on a stored procedure node, then right-click and from the context menu choose Debug Routine.

Answer 11

MySql Connector/NET also includes a stored procedure debugger integrated in visual studio as of version 6.6, You can get the installer and the source here: http://dev.mysql.com/downloads/connector/net/

Some documentation / screenshots: https://dev.mysql.com/doc/visual-studio/en/visual-studio-debugger.html

You can follow the annoucements here: http://forums.mysql.com/read.php?38,561817,561817#msg-561817

UPDATE: The MySql for Visual Studio was split from Connector/NET into a separate product, you can pick it (including the debugger) from here https://dev.mysql.com/downloads/windows/visualstudio/1.2.html (still free & open source).

DISCLAIMER: I was the developer who authored the Stored procedures debugger engine for MySQL for Visual Studio product.

Answer 12

The first and stable debugger for MySQL is in dbForge Studio for MySQL

Answer 13

I had use two different tools to debug procedures and functions:

1. dbForge - many functional mysql GUI.
2. MyDebugger - specialized tool for debugging ... handy tool for debugging.vote http://tinyurl.com/voteimg

Answer 14

MySQL user defined variable (shared in session) could be used as logging output:

DELIMITER ;;
CREATE PROCEDURE Foo(tableName VARCHAR(128))
BEGIN
  SET @stmt = CONCAT('SELECT * FROM ', tableName);
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;
  -- uncomment after debugging to cleanup
  -- SET @stmt = null;
END;;
DELIMITER ;

call Foo('foo');
select @stmt;

will output:

SELECT * FROM foo

Answer 15

Toad mysql. There is a freeware version http://www.quest.com/toad-for-mysql/

Answer 16

Answer corresponding to this by @Brad Parks Not sure about the MySQL version, but mine was 5.6, hence a little bit tweaking works:

I created a function debug_msg which is function (not procedure) and returns text(no character limit) and then call the function as SELECT debug_msg(params) AS my_res_set, code as below:

CREATE DEFINER=`root`@`localhost` FUNCTION `debug_msg`(`enabled` INT(11), `msg` TEXT) RETURNS text CHARSET latin1
    READS SQL DATA
BEGIN
    IF enabled=1 THEN
    return concat('** DEBUG:', "** ", msg);
    END IF;
END

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_func_call`(
 IN RegionID VARCHAR(20),
 IN RepCurrency INT(11),
 IN MGID INT(11),
 IN VNC VARCHAR(255)
)
BEGIN
    SET @enabled = TRUE;
    SET @mainQuery = "SELECT * FROM Users u";
    SELECT `debug_msg`(@enabled, @mainQuery) AS `debug_msg1`;
    SET @lastQuery = CONCAT(@mainQuery, " WHERE u.age>30);
    SELECT `debug_msg`(@enabled, @lastQuery) AS `debug_msg2`;
END $$
DELIMITER