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.