Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?

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
                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?
– Patrick M
                Jul 28, 2015 at 21:29
                @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)
– Xenos
                Jul 6, 2017 at 8:42
                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
– mustafa kemal tuna
                Jun 14, 2020 at 5:46
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.
                Not all platforms @Bob Probst , sybase debugging tools are quiet decent with breakpoint debug for trigger and stored procedures
– Anup
                Jun 10, 2015 at 11:03
Create a table called logtable with two columns, id INT and log VARCHAR(255).
Make the id column autoincrement.  
Use this procedure:
delimiter //
DROP PROCEDURE `log_msg`//
CREATE PROCEDURE `log_msg`(msg VARCHAR(255))
BEGIN
    insert into logtable select 0, msg;
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.
                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.
– kellogs
                Oct 8, 2013 at 2:26
                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.
– Steve Chambers
                Aug 4, 2016 at 9:28
                Difficult to find what platforms that debug tool runs on. Seems to run on Windows. Anything else?
– Guy
                Jul 14, 2016 at 17:20
Create Procedure resetLog() 
BEGIN   
    create table if not exists log (ts timestamp default current_timestamp, msg varchar(2048)) engine = myisam; 
    truncate table log;
DROP PROCEDURE IF EXISTS doLog 
Create Procedure doLog(in logMsg nvarchar(2048))
BEGIN  
  insert into log (msg) values(logMsg);
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;
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'm late to the party, but brought more beer:
http://ocelot.ca/blog/blog/2015/03/02/the-ocelotgui-debugger/
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:
                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.
– user1710989
                Nov 6, 2020 at 10:14
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 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.
                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...
– Hooman Bahreini
                Dec 18, 2019 at 2:27
                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.
– Fernando Gonzalez Sanchez
                Dec 18, 2019 at 21:51
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
                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?
– Cory House
                Jun 5, 2012 at 11:54
                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?
– Joyce
                Jun 5, 2012 at 16:26
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;
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.