Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted July 9, 2015

Debugging MySQL Stored Procedures

By Rob Gravelle

Working the kinks out of stored procedures can be a truly frustrating experience.  Outputting to the console works but can be a slow going process.  Some IDEs, such as Visual Studio have SQL debugging capabilities, but what about the rest of us who don’t use a specialized IDE.  Recently, I came across a stand-alone GUI MySQL debugger that really impressed me.  Aptly named “Debugger for MySQL”, I found this inexpensive tool to be quite easy to get the hang of, since it looks and behaves like most programming debugging environments.  In today’s article, I’ll give a rundown on how to use it on your stored procedures.

Overview

In the interest of full disclosure, I did receive a licence to fully evaluate the product.  It does come with a 14-day trial, but, with my schedule being what it is lately, I took over two months to get through it!  

I’m not going to go into tremendous detail here because you can read all about the product on the company website, but here are the main features:

  • Efficiently debugs and manages stored MySQL/MariaDB procedures and functions
  • Debugs nested procedures and functions as well as anonymous code (similar to anonymous blocks in Oracle)
  • Supports conditional breakpoints
  • Features tooltip expression evaluation
  • Runs SQL commands in the debugging context
  • The smart code editor provides autocompletion
  • Full Unicode support

There are two kinds of licenses:

  • Debugger for MySQL with 1 year free updates   $49.00 USD
  • Debugger for MySQL with 3 years free updates $99.00 USD

There are several payment methods: Credit Card, PayPal, Bank/Wire Transfer, Check/Money Order, Fax, and Invoice.  All transactions are conducted using a secure payment process.

Debugging a Stored Procedure

So let’s get right into what you’re reading this article for: how to debug your stored procedures.

We will be using the sakila sample database that was introduced in the Generating Reports on MySQL Data tutorial.  If you would like to follow along, you may want to set it up before proceeding.

Upon launching the application, you’ll be greeted by the Connection dialog.

  • Enter the connection information for the sakila schema and click the Test connect button to make sure that everything is correct:

    Connection
    Connection

  • Once you get the “Connection succeeded!” message, click OK to proceed.

When the main application first appears, it will contain four windows: the <Main> Stored Procedure editor, Schema browser, Call stack, and Output.

Stored Procedure Editor
Stored Procedure Editor

Note that the <Main> Stored Procedure contains instructions on how to start debugging.  You can’t just run a procedure using the Run (>) button on the main toolbar because Stored Procedures typically receive input parameters.  For that reason, you have to configure the launch environment first.  Let’s do that now, using the “rewards_report” procedure.

To configure the launch environment:

  • Right-click the rewards_report  procedure in the Schema browser and select Configure environment for debug this routinefrom the popup menu:

    Schema Browser
    Schema Browser

  • A dialog will ask you to confirm; click Yes.
  • A second dialog will appear to let you know that the environment was configured successfully and to press the F9 key to begin debugging the procedure.   Click OK to close the dialog.

Configuring the environment does two things.  It

  1. sets a bright orange breakpoint on the BEGIN statement of the procedure;
  2. places a call to the proc in the <Main> editor:
BEGIN
   SET @min_monthly_purchases = NULL;
   SET @min_dollar_amount_purchased = NULL;
   CALL `sakila`.`rewards_report`(@min_monthly_purchases, 
                      @min_dollar_amount_purchased, @count_rewardees);
   SELECT @count_rewardees;
 END

The Debugger for MySQL handles input parameters by setting them to an acceptable default such as NULL.  You can change these values before debugging the procedure.

BEGIN
   SET @min_monthly_purchases = 5;
   SET @min_dollar_amount_purchased = 100.0;
   CALL `sakila`.`rewards_report`(@min_monthly_purchases, 
                      @min_dollar_amount_purchased, @count_rewardees);
   SELECT @count_rewardees;
 END

Creating a New Procedure

Though not necessary for the purposes of this tutorial, you can also create a new stored procedure as follows:

  • Select File > Create procedure… from the main menu.
  • On the Create procedure dialog, enter a name in the Enter procedure name field and click OK to create the new procedure.

At this point, a new Editor tab will appear and the new procedure will be appended to the Procedures in the Schema browser:

Procedures
Procedures

  • Click the Store routine button on the main toolbar to save your procedure.

Toggling a Breakpoint

To toggle a breakpoint on or off, click to the left of the line in the grey margin.  Note that lines that contain actions are identified by a small blue dot.  Breakpoint s may be set on any line, but execution of the procedure will not stop on lines where there is no blue dot. 

The Debugging Process

  • Start the procedure by clicking the Run button on the main toolbar or by pressing the F9 key.
    The debugger will then stop on the first breakpoint.  It will turn teal to show that execution is stopped.  There is also a green arrow in the margin that identifies the current line:

The debugger will then stop on the first breakpoint
The debugger will then stop on the first breakpoint

To proceed through the code, you can either press the Step Over (>>) or Step Into (>|) button. The difference between the two is that Step Into will cause the code of an invoked user function or stored procedure to open in the editor, while Step Over simply performs the call.

Showing Variables

All declared variables and their values may be examined in the Local variables tab at the bottom of the application. As you step through the code, the Values column will reflect a variable’s current value.

Evaluating Expressions

Variables and more complex expressions may be evaluated in a number of ways.  For one-time evaluations, you can select the pertinent code and simply hover the mouse pointer over it.  In a second, a tooltip will appear with the selected expression as well as its value:

Tooltip
Tooltip

For a more permanent reference:

  • Copy the selection.
  • Right-click anywhere in the Watch list pane (it’s the first tab at the bottom of the application) and select Add… from the popup menu.
  • Paste it into the Add watch dialog and click OK to add it to the Watch list.

Of course, you are free to type any expression that you’d like to evaluate.

The Call Stack

Each time that your procedure (or user function) calls another stored procedure or user function, it is added to the top of the Call stack.

Adding Breakpoint Conditions

The Breakpoint list tab contains all of the breakpoints for the current session.

Unchecking the box beside a breakpoint will disable it.  Disabled breakpoints turn gray in the Code editor.

You can also set a condition on a breakpoint so that it only stops under certain circumstances.  To do that:

  • Right-click on the breakpoint in the Breakpoint list tab and select Properties… from the popup menu.
  • In the Properties dialog, enter an evaluation expression in the Condition textbox.  Examples include “min_dollar_amount_purchased > 50.0” or “min_monthly_purchases IS NOT Null”

Viewing the Results

When a query is executed, its results are appended to the list in the Output pane. Those that return a result set may be viewed by right-clicking on it and selecting Show results from the popup menu:

Viewing the Results
Viewing the Results

Conclusion

Debugger for MySQL is a prime example of a tool that does one thing and does it well.  Thus, I found that Debugger for MySQL really helps me to work through complex conditional, iterative, and/or computationally intensive logic.  For other tasks like writing complex queries, I would turn to another specialized tool such as a Graphical Query Designer.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM