[mysql] How do you debug MySQL stored procedures?

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?

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.

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.

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:

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

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.

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

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.

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

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?

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.

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

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

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

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

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.

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.

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:

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

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.

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

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?

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;

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.

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.

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

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

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

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

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