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.
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.
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:
- 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
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:
- 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
- sets a bright orange breakpoint on the BEGIN statement of the procedure;
- 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:
- 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
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.
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.
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:
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
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.