Debugging MySQL Stored Routines

Content


Introduction


The release of MySQL 5.0 was marked by the introduction of stored routines (procedures, functions and triiggers) 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 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.

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.