你的位置:首页 > 软件开发 > 数据库 > How to DEBUG a trigger or procedure

How to DEBUG a trigger or procedure

发布时间:2016-09-09 06:00:08
DEBUGGING STORED PROCEDURESOver the past several weeks, we’ve been working on debugging a stored procedure bug for a client. Comin ...

DEBUGGING STORED PROCEDURES

Over the past several weeks, we’ve been working on debugging a stored procedure bug for a client. Coming from a software development background, I looked at the procedure like any piece of code — how can I debug the program and be able to use some means of knowing the values within the program while it’s running? In C, I’ve always used GDB, as well as Perl having it’s own debugger. Also useful are print statements! Those can be the most simplistic but also useful tools in debugging, especially in the case of a C program that’s so optimized that GDB gives you the famous “value optimized out” message, preventing you from knowing what the program is really doing.

Stored procedures are a whole different matter than compiled or interpreted code. They are executing within the database. The line numbers in your source file you inevitably create the procedure with don’t match the line numbers of the procedure as stored by the database. Furthermore, if you are a developer of other languages, you will find debugging stored procedures to be a exercise in misery. You can load a stored procedure into MySQL, and as long as the SQL is syntactically correct, it will not catch things such as specifying variables that don’t exist– you will have the joy of discovering those during run-time– and most often the error message displayed will be of little use.

How then, can you better observe the execution of a stored procedure?

In some cases, you could run it with print statements and simply observe the output when the procedure is executed. But in our case, that was not a solution as the procedure had mostly DML statements and we needed something that was more precise. We needed something that not only allows you to display the values of variables, but also show you when the statement was executed.

There is this very useful article on Dr. Dobbs, a concept that we expanded upon. The main idea of this article is that a temporary memory table is used during the execution of the stored procedure to insert stored procedure debug messages into, and being a memory table, won’t touch disk and will be as lightweight as possible. At the end of a debug session, these messages are copied from the temporary table into a permanent table with the same table definition. This gives you a means of reading the debug messages written during the execution of your stored procedure.

We built upon the idea from the article and came up with these useful stored procedures that are easy to load and utilize in a stored procedure. They allow you to print any message that you want to, along with the time and the connection/thread ID of the thread executing the procedure– something that was invaluable in debugging what we thought at first was a race condition.

Debugging stored procedures

The following debugging procedures can be used within your stored procedures. First, each will be introduced, and then a more descriptive explanation on how they work will be provided, along with source code. Note that these procedures are also available github:

http://github.com/CaptTofu/Stored-procedure-debugging-routines

This post was written to help those who are pulling their hair out debugging their stored procedures. This post was also written for those who might have come from more from a development role and might have an approach that is overly complex. When debugging stored procedures, here are some tips that will help:

* binary log – look at this first when something seems awry. It is the closed-circuit TV recording of what happened with your database

原标题:How to DEBUG a trigger or procedure

关键词:

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

可能感兴趣文章

我的浏览记录