Debugging MySQL Stored Routines
Content
- Introduction
- Approaches of debugging stored routines
- dbForge Studio for MySQL debugger structure
- Debugging stored procedures, functions, and triggers using dbForge Studio for MySQL
- Conclusion
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.
