Debugging MySQL Stored Routines
Content
Introduction
The release of MySQL 5.0 was marked by the introduction of stored routines (procedures,
functions and triggers) support. This was a huge milestone for the popular database
company, which made it possible to use MySQL databases in complicated business projects.
However, even the most recent versions of MySQL do not provide facilities for debugging
stored routines or API for controlling stored code. The only way to debug a stored
program with MySQL 5.x is to manually fill it with tracing instructions that save
messages in a table on the server. Such a debugging process is uncontrolled and
non-interactive. This paper will present a new debugging solution which overcomes
this limitation of MySQL and allows developers to conveniently debug stored routines,
making the MySQL development process more comfortable and more effective.
Approaches of debugging stored routines
Several different approaches for debugging stored routines can be found on the Web,
but until recently there were no real end-user solutions for debugging. The first
workaround is to create an external stored routine interpreter similar to
the one in MySQL and use it for debugging. This method is good because it makes
it possible to debug all types of stored code since it gives the programmer a lot
of flexibility and does not place any restrictions on controlling code execution.
However, this approach also has a big drawback – any self-written interpreter
will differ from native MySQL interpreter, so its logic will be different in some
cases even if SQL 2003 standard will be implemented, and the developer may end up
spending a couple of wonderful hours trying to understand why procedure execution
and debugging behave differently.
A second approach is to create a server-side debug engine with facilities
for controlling execution of stored routine and notifying the client about the execution
state. Implementing this debug engine concept would require creating a special debugging
database on the server and inserting debug calls into a stored routine before debugging.
Stored routine must be prepared for this type of debugger, by inserting debug statements
into it's source code, but this approach would not cause problems related to incorrect
non-native stored code interpretation, and debug process would be clear.
Because of this discussion we decided to use the server-side debug engine approach
for building a stored routine debugger for MySQL, it is more adequate than the interpreter
approach.
dbForge Studio for MySQL debugger structure
The Debugger we implemented consists of two parts (see Figure 1).
-
A Server part – Debug engine. A database with stored procedures for controlling
debugging and tables for debug interaction and variable watch updates.
-
A Client part – A GUI for debugging.
Figure 1. Debugger concept.
The Debug Engine allows the logic on the client side to integrate debugging stored
procedures calls into the code of the target stored routine, and can suspend the
debugging process at any time. It also updates information about the debugging process
and updates watch values.
The Development GUI performs the compilation of target procedure with debug
information by automatically inserting debugging calls into the stored routine source
code, and the reverse compilation of the procedure, function, or trigger
by removing the debug calls from the source code. The Development GUI launches the
debug process and lets the user control it with Step through commands and Breakpoints.
It also provides all the debug support, such as highlighting of the current statement
and Call Stack and Watches functionality.
The debugger uses two connections to MySQL server – one for executing target
stored routine and another for controlling debugging by setting control flags in
the control table and gathering information about the debug process (see Figure
2).
Figure 2. Debugging communication.
Debugging stored procedures, functions and triggers using dbForge Studio for MySQL
We implemented a debugger using the chosen approach in dbForge Studio for MySQL.
Before debugging you have to deploy the Debug Engine to the server (see Figure 3).
dbForge Studio for MySQL will create cr_debug database and fill it with stored routines
and tables. Then you have to prepare your procedure, function, or trigger for debugging.
Figure 3. Deploy Debug Engine
You have to compile it with debug info (see Figure 4). MySQL doesn't
support compilation of stored routines; however, we will use this concept here to
refer to the automatic integration of debug calls into the code of a routine. dbForge
Studio for MySQL will parse stored routine source and insert debug calls in right
places automatically. This changes are transparent to the user, and do not show
up in the procedure editor.
You can remove debug calls from the procedure by recompiling the procedure using
the standard “Compile” command. Procedure logic is not influenced by
debug calls, so you can also execute procedure after exiting debug mode without
recompiling.
Figure 4. Compilation.
The dbForge Studio for MySQL debugger has all the facilities for comfortable
debugging, including support for Stepping through code, Breakpoints, and
a Call Stack for tracking calls to nested procedures. Breakpoints allow you
to break running stored procedure in any place you want. You can manipulate breakpoints
from editor and breakpoints window.
Figure 5. Debugging UI.
The Watches window allows you to evaluate variables and parameters which
are declared in your routines. Watches are bound to stack frames and let you analyze
the contexts of your stored applications. In this way, the implemented Debugger
provides all the functionality necessary for automating the process of debugging
stored routine with MySQL.
Conclusion
The integration of a stored routine debugger into dbForge Studio for MySQL presents
a powerful tool for MySQL developers and DBAs. In the future we plan to improve
debugging by providing more close integration with data access solutions for .NET
and VCL. Stepping through stored code in C#, C++, or Delphi will soon be made available
in dbForge Fusion for MySQL, an add–in version
of the IDE for Microsoft Visual Studio and CodeGear RAD Studio.
We hope you will not have to debug your applications too often, but if you really
need to, now debugging MySQL stored procedures will be the same as debugging your
favorite language programs in modern IDE. Just deploy the Debug Engine to
the server, compile your procedure with debug information, and enjoy
single–click stepping through code, setting breakpoints, viewing the call
stack and watches.