DebuggingStoredRoutines
Author: Elias Kohen (mysqldev@crlab.com) Core Lab Development Team
Contents |
[edit] 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.
[edit] Approaches
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 is not necessary to deploy any additional code to the server and to compile debugging units. 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 nonnative 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.
[edit] 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 you 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.
[edit] Debugging in MySQL Developer Studio
We implemented the debugger using the chosen approach in Core Lab's MySQL Developer Studio 2.00. Before debugging you have to deploy the Debug Engine to the server as indicated in the Figure 3. MySQL Developer Studio 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. MySQL Developer Studio will parse stored routine source and insert debug calls in right places automatically. This changes are transparent 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 MySQL Developer Studio 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.