Featured Database Articles

Debugging MySQL Stored Procedures

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:

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

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:

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

sets a bright orange breakpoint on the BEGIN statement of the procedure;

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

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.

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

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.

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

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.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.